JOINs

A JOIN combines rows from two tables by a condition. The type of JOIN decides what to do with a row that finds no match.

INNER JOIN: only rows that have a match

Order 109 belongs to a customer_id that does not exist; it disappears from the INNER JOIN.

SELECT o.id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.order_date = '2024-06-05';

id  | name | amount
--- | ---- | ------
110 | Ana  | 900

LEFT JOIN: everything from the left

Every customer appears; one who did not buy comes back with NULL in the order columns.

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (5, 6);

name  | order_id
----- | --------
Elisa | 107
Fábio | NULL

A filter in the wrong place turns it into an INNER JOIN

A condition on the right-hand table in the WHERE discards the rows with NULL and undoes the LEFT JOIN. If the filter is part of the "matching", it goes in the ON.

SELECT 'in WHERE' AS location, COUNT(DISTINCT c.id) AS customers
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
UNION ALL
SELECT 'in ON', COUNT(DISTINCT c.id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered';

location | customers
-------- | ---------
in WHERE | 5
in ON    | 6

Anti-join: rows with no match

The clearest way to find orphans is NOT EXISTS. Here, the orders without a customer (a data quality problem) and the customers without an order.

SELECT 'order without customer' AS case_name, o.id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)
UNION ALL
SELECT 'customer without order', c.id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

case_name              | id
---------------------- | ---
order without customer | 109
customer without order | 6

A JOIN that multiplies rows

If the key is not unique on the other side, each row matches several. Summing after a JOIN like that counts the same amount twice: order 101 has two items, so its amount shows up twice.

SELECT SUM(o.amount) AS correct_sum,
       (SELECT SUM(o2.amount) FROM orders o2 JOIN order_items i ON i.order_id = o2.id WHERE o2.id = 101) AS sum_after_join
FROM orders o
WHERE o.id = 101;

correct_sum | sum_after_join
----------- | --------------
230         | 460

• In Spark, the anti-join has its own name: LEFT ANTI JOIN; and the semi-join is LEFT SEMI JOIN.
• Before joining, check the cardinality: COUNT(*) against COUNT(DISTINCT key) on the other side.