Part 3 of 5 · 1 min read
Left and right join
Keeping every row from one side, with NULLs filled in for no match.
Think of a class attendance sheet listing every student, even the ones absent that day, with a blank mark instead of simply leaving absent students off the sheet entirely.
In plain words: a left join keeps every row from the left table, filling in NULL for columns from the right table when no match exists. A right join does the same thing in the other direction, keeping every row from the right table. A left join and a right join with the two tables swapped produce the same result; most people pick one convention and stick to it.
A worked example. Listing every customer, including those with no orders, shows NULL in the order columns for a customer with none.
The key lines.
SELECT c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
Left join, right join
A left join keeps every row of the left table, with NULL filling unmatched right-side columns. A right join keeps every row of the right table, with NULL filling unmatched left-side columns.
Example: a customer with zero orders appears once in the left join above, with o.order_id shown as NULL.