In this tutorial, we will learn about MySQL OUTER JOIN. An INNER JOIN will find combinations of matching rows from joined tables, whereas the OUTER JOIN also finds the instances where a row in one table has no match in another table.
More information on INNER JOIN:
Outer Joins can be
- LEFT JOIN
- RIGHT JOIN
These do not use the comma separator. The left table and right table refer to the tables named first
and second in the FROM clause, respectively. Note that, a LEFT JOIN can always be re-written into an equivalent RIGHT JOIN and vice versa.
The LEFT JOIN produces a result for each row in the left table. whether or not it has any match in the right join table.
In this example, we will use the Country and CountryLanguage tables from the MySQL world database. Let’s find out which countries in the Country table are not associated with any language in the CountryLanguage table.
We need to identify which country codes present in the Country table are not present in the CountryLanguage table. NULLs will be returned for non-existent rows of a column.
mysql> SELECT Name, Language
-> FROM Country
-> LEFT JOIN CountryLanguage
-> ON Code = CountryCode
-> WHERE CountryCode IS NULL;
| Name | Language |
| Antarctica | NULL |
| French Southern territories | NULL |
| Bouvet Island | NULL |
| Heard Island and McDonald Islands | NULL |
| British Indian Ocean Territory | NULL |
| South Georgia and the South Sandwich Islands | NULL |
6 rows in set (0.00 sec)
RIGHT JOIN is the reverse of the LEFT JOIN. The RIGHT JOIN produces a result for each row in the right table. whether or not it has any match in the left join table.
MySQL Tutorials on this website:
For more information on MySQL Database: