OUTER JOIN: finding the "gaps"

In Module 3 you saw LEFT JOIN: keep all rows on the left, even those without a match on the right (which become NULL). In this chapter we expand the toolkit with RIGHT JOIN and FULL OUTER JOIN, and we learn how to use them to find the "holes" in the data: rows in one table that have no counterpart in the other.

The three kinds of OUTER JOIN

A LEFT  JOIN B  ON …  -- tutte le righe di A, con o senza match in B
A RIGHT JOIN B  ON …  -- tutte le righe di B, con o senza match in A
A FULL  JOIN B  ON …  -- tutte le righe di A *e* di B; NULL dove non c'è match

A RIGHT JOIN B can always be rewritten as B LEFT JOIN A with the same ON: it's a matter of readability. Many styles (including this course) prefer to always use LEFT JOIN for consistency.

The "anti-join" pattern: finding the missing ones

The most common use of LEFT JOIN is not "enrich with the data on the right", but find the rows on the left that do NOT have a match on the right. It's called an anti-join:

-- Prodotti senza nessuna recensione:
SELECT p.id, p.name
FROM   products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE  r.id IS NULL
ORDER BY p.id;

Pattern logic:

1. LEFT JOIN attaches each product's reviews, if any;
2. for products without reviews, all the r columns are NULL;
3. WHERE r.id IS NULL keeps only those products.

Key check: an id IS NULL on a NOT NULL column of the right-hand table means "there was no match". You often filter on the PK of the right-hand table precisely for this reason.

FULL OUTER JOIN

FULL OUTER JOIN keeps both sides without a match. It's rarely used, typically for data integrity checks:

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

Rows with c.id IS NULL would be orphan orders (a customer_id that doesn't exist in customers). Those with o.id IS NULL are customers without orders. On our dataset the foreign keys guarantee that no orphan orders exist; only the customers without orders remain.

Try it yourself

Review exercise