LEFT and RIGHT JOIN
An INNER JOIN is exclusive: it only returns rows that have a perfect match in both tables. But what if you want to see data even when there is no match?
For example, what if you want a list of all users, including the ones who have never bought anything? This is where Outer Joins—specifically LEFT JOIN and RIGHT JOIN—come in.
The LEFT JOIN
A LEFT JOIN (sometimes called a LEFT OUTER JOIN) returns all records from the “left” table, and the matched records from the “right” table.
Which table is the “left” table? It’s simply the one you list first (in the FROM clause). The “right” table is the one listed second (in the JOIN clause).
SELECT users.name, orders.id AS order_number
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;In this scenario:
- Alice and Bob have placed orders, so their names and order numbers will appear normally.
- Charlie and Diana have never placed an order. Because we used a
LEFT JOINstarting with theuserstable, Charlie and Diana will still appear in the results! However, because they have no matching data in theorderstable, the database will fill theorder_numbercolumn withNULLfor their rows.
Finding the Non-Matches
A very common trick is to use a LEFT JOIN in combination with a WHERE ... IS NULL clause to find orphaned records.
To find a list of users who have zero orders:
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;This is a powerful way to identify inactive users or missing data.
The RIGHT JOIN
A RIGHT JOIN is exactly the same concept, just flipped. It returns everything from the “right” (second) table, regardless of whether it matches the “left” table.
In practice, RIGHT JOIN is rarely used. Why? Because it’s easier for humans to read from top-to-bottom/left-to-right. Instead of using a RIGHT JOIN, developers almost always just swap the order of the tables and use a LEFT JOIN instead.
Try using a LEFT JOIN in the lab to help the marketing team find out which users haven’t placed orders yet!