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.