LEFT, RIGHT & FULL OUTER JOIN

LEFT JOIN is an outer join, a join that keeps unmatched rows instead of dropping them. It keeps every row from the left table, matches what it can from the right table, and fills the right-side columns with NULL when no match exists.

That is the answer to 5.2's closing question: all customers, with their orders if any.

LEFT JOIN keeps the left side

The left table is the table before the LEFT JOIN keywords. Start from customers, then match orders:

SELECT c.id AS customer_id,
       c.name,
       o.id AS order_id,
       o.product
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
ORDER BY c.id, o.id;

 customer_id |   name   | order_id |     product
-------------+----------+----------+------------------
           1 | Ada      |      101 | Espresso Machine
           1 | Ada      |      102 | Grinder
           2 | Grace    |      103 | Kettle
           3 | Linus    |      104 | Espresso Machine
           3 | Linus    |      105 | Filter Papers
           4 | Margaret |     NULL | NULL
           5 | Edsger   |      106 | Kettle
           5 | Edsger   |      107 | Scale
           6 | Barbara  |     NULL | NULL
(9 rows)

A join is still a match-maker, not a merge. The difference is what happens when the match-maker fails. INNER JOIN drops unmatched rows. LEFT JOIN keeps the left row and writes NULL into every selected right-table column.

That makes LEFT JOIN the reporting join. If your question starts with "all customers," put customers on the left.

Finding the missing

An anti-join is a join pattern that finds rows with no match. The shape is LEFT JOIN, then WHERE right_table.id IS NULL:

SELECT c.id,
       c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

 id |   name
----+----------
  4 | Margaret
  6 | Barbara
(2 rows)

Read it in two passes. First, LEFT JOIN keeps every customer and fills unmatched order columns with NULL. Then WHERE o.id IS NULL keeps only the customers whose order side stayed empty.

Use the right table's primary key for this pattern when you can. If o.id is NULL after the join, the order row is missing. Checking a column that is allowed to be NULL can lie.

The classic trap: WHERE on the right side

Here is the report you mean to write: all customers, with large orders if any. The first attempt looks reasonable:

One useful rule: WHERE c.city = 'Berlin' is a left-table filter, so it intentionally chooses which customers to report on. WHERE o.amount >= 100 is a right-table filter, so it can throw away the NULL-filled rows you kept the LEFT JOIN for.

RIGHT JOIN: same idea, harder to read

A RIGHT JOIN keeps every row from the right table, the table after the JOIN keyword. This query keeps every customer because customers AS c is on the right:

SELECT c.name,
       o.id AS order_id,
       o.product
FROM orders AS o
RIGHT JOIN customers AS c
  ON o.customer_id = c.id
ORDER BY c.id, o.id;

   name   | order_id |     product
----------+----------+------------------
 Ada      |      101 | Espresso Machine
 Ada      |      102 | Grinder
 Grace    |      103 | Kettle
 Linus    |      104 | Espresso Machine
 Linus    |      105 | Filter Papers
 Margaret |     NULL | NULL
 Edsger   |      106 | Kettle
 Edsger   |      107 | Scale
 Barbara  |     NULL | NULL
(9 rows)

Most teams rewrite RIGHT JOIN as LEFT JOIN by putting the kept table first. Humans read queries top to bottom; the table your question starts with should usually appear first.

FULL OUTER JOIN: keep both sides

A FULL OUTER JOIN keeps unmatched rows from both tables. It is useful for reconciliation, which means comparing two sides and listing what does not line up.

Here, the café wants rows that exist on only one side of the customer/order relationship: customers with no orders, and orders with no customer.

SELECT c.name,
       o.id AS order_id,
       o.product
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL
ORDER BY c.id, o.id;

   name   | order_id |  product
----------+----------+-----------
 Margaret |     NULL | NULL
 Barbara  |     NULL | NULL
 NULL     |      108 | Gift Card
(3 rows)

The NULL customer name on the last row is the clue: the order side exists, but no customer matched it. The NULL order columns above it mean the customer side exists, but no order matched it.

Choosing the join

Use the join that matches the row you promise to keep:

• Need only matched pairs? Use INNER JOIN.
• Need all rows from your main table, plus matches if they exist? Put that table first and use LEFT JOIN.
• Need rows from the table currently on the right? Rewrite the query as a LEFT JOIN with that table first.
• Need rows missing from the other table? Use the anti-join pattern: LEFT JOIN, then WHERE right_table.id IS NULL.
• Need to compare both sides and keep unmatched rows from either side? Use FULL OUTER JOIN.

Outer joins give you control over which unmatched rows survive. Next, the same match-maker idea gets stranger: joining a table to itself, asking for every combination, and walking through many-to-many junction tables without losing the grain.