Right, full outer, and cross joins

Let's complete the JOIN family. You already know INNER and LEFT. Now meet RIGHT JOIN (mirror of LEFT), FULL OUTER JOIN (keep everything), CROSS JOIN (every combination), and self joins (a table joined to itself).

The complete join family

RIGHT JOIN is almost never used because you can always rewrite it as a LEFT JOIN by swapping the table order. FULL OUTER JOIN is rare but invaluable for data reconciliation, like comparing two data sources to find mismatches on both sides.

right_join.sql
sql
-- RIGHT JOIN is just LEFT JOIN flipped
-- These two queries produce identical results:

-- LEFT JOIN version
SELECT p.product_id, oi.order_id
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

-- RIGHT JOIN equivalent
SELECT p.product_id, oi.order_id
FROM order_items oi
RIGHT JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

RIGHT JOIN keeps all rows from the right table. In practice, most people use LEFT JOIN and just swap the table order.

RIGHT JOIN is rarely used since you can always rewrite it as a LEFT JOIN by swapping table order. FULL OUTER JOIN, however, is uniquely useful when you need all rows from both sides.

full_outer.sql
sql
-- FULL OUTER JOIN: all customers + all orders
-- Even customers with no orders AND orders with no customer
SELECT
  c.customer_id,
  o.order_id,
  o.order_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL
LIMIT 20;

FULL OUTER JOIN keeps all rows from both tables. NULLs fill in where there is no match on either side.

CROSS JOIN is different from the others because it does not use an ON clause. It produces every possible combination of rows, which is useful for generating scaffolds.

cross_join.sql
sql
-- CROSS JOIN: every state x payment type combination
SELECT DISTINCT
  c.customer_state,
  p.payment_type
FROM (
  SELECT DISTINCT customer_state FROM customers
) c
CROSS JOIN (
  SELECT DISTINCT payment_type FROM order_payments
) p
ORDER BY c.customer_state, p.payment_type;

CROSS JOIN produces the Cartesian product: every possible combination of rows. Useful for creating scaffolds (all state x type combos) that you can LEFT JOIN actual data onto.

Quiz: Quiz

Loading practice…

Checkpoint: Joins checkpoint

Loading practice…