The OUTER JOIN Clause

The Outer Join Clause

An outer join will return all the rows from one table and only the rows from the other table that match the join condition
You can use LEFT JOIN or RIGHT JOIN. If you use LEFT JOIN, all the rows from the table on the left of the equals ( = ) sign will be included in the result set whether the join condition is satisfied or not
If you use RIGHT JOIN, all the rows from the table on the right of the equals ( = ) sign will be included in the result set whether the join condition is satisfied or not.

Below is a code snippet of a SQL statement with an outer join clause.

1 USE world; 2 SELECT c.name, c.continent, cl.language 3 FROM country c LEFT JOIN countrylanguage cl 4 ON c.code = cl.CountryCode 5 ORDER BY cl.language ASC;

Results:

SELECT c.name, c.continent, cl.language

The “c.” pre-pended to name and continent is a table alias to the country table. Therefore,
return name and continent from the country table.
The “cl” prepended to the language table is a table alias to the countrylanguage table.
Therefore, return language from the countryLanguage table.

FROM country c LEFT JOIN countrylanguage cl

“Country c” assigns “c” as an alias for “country”
“countrylanguage cl” assigns “cl” as an alias for “countrylanguage”
LEFT JOIN means that all rows on the left side of the JOIN operator ( = ) are included in the
results whether they have a matching key from the table on the RIGHT side of the operator.

ON c.code = cl.CountryCode

ON is the second part of the JOIN clause. It precedes the JOIN condition
c.code refers to the code column from the country table and is a primary key. Since the key is on
the LEFT side of the join condition, all rows from the country table will be included in the results
whether they have a matching key in the countrylanguage table or not.
Cl.CountryCode refers to the CountryCode on the countrylanguage table and is a foreign key to
the country table. Only the rows that have a matching key in the country table will be included
in the results.