The OUTER JOIN Clause

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.