Learning MySQL By Example
2.3
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.
CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.
End-of-Chapter Survey
: How would you rate the overall quality of this chapter?- Very Low Quality
- Low Quality
- Moderate Quality
- High Quality
- Very High Quality
Like this? Endorse it!