MySQL OUTER JOIN Tutorial & Examples
Here you find information about OUTER JOINs in MySQL statements. This tutorial is a general introduction. Syntax details and example statements are split into a LEFT OUTER JOIN tutorial and RIGHT OUTER JOIN tutorial because these are specific outer joins.
Outer Join basics
MySQL supports outer joins. They identify matching rows that are stored in two different tables and add the selected columns to the result set. That’s exactly what an inner join does as well.
The difference between inner and outer join is: An outer join can identify rows without a match in the joined table. When no match was found, MySQL sets the value of columns from the joined table to NULL. This is important for example when you want to select all records from a reference table that have no related data in another:
- select all orderids from a table “orders” where no entry in a table “shipping” exists
- delete records from a table if they have no related records in another table
- and things like that.
To understand which table is treated as the reference table, we first need to understand which table is called the “left table” and which table is called the “right table”. It’s actually as easy as:
- The left table is listed on the left side of the OUTER JOIN keywords
- The right table is listed on the right side of the OUTER JOIN keywords
Here is an example:
SELECT * FROM leftTable LEFT JOIN rightTable USING(id)
Left Join or Right Join?
To indicate the reference table in an outer join statement, MySQL separates between LEFT JOIN and a RIGHT JOIN. The left table is the reference table in a LEFT JOIN and right table in a RIGHT JOIN. Please see the following links for more information about the specific outer joins:
Just keep in mind: a left join can also be written as a right join. They basically answer the same questions, you just have to ask. However, it’s generally common to write an outer join as a left join in case you read normal text left-to-right as well.