Joins beyond the basics: LATERAL, DISTINCT ON, semi- and anti-joins

The SQL course covers inner and outer joins. This lesson is about the three PostgreSQL patterns that interview questions about "the latest", "the top two" and "has any" are really testing.

LATERAL: a subquery that can see the current row

An ordinary subquery in FROM is evaluated on its own; it cannot refer to a table listed before it. LATERAL lifts that rule: the subquery runs once per row of the left side and may use its columns. That is exactly what "for each customer, their two largest orders" needs, because LIMIT must apply per customer, not to the whole result.

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount numeric(10,2) NOT NULL);3INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');4INSERT INTO orders VALUES5  (10, 1, 50), (11, 1, 120), (12, 1, 80), (13, 2, 30), (14, 2, 30), (15, 1, 120);

1SELECT c.name, o.id AS order_id, o.amount2FROM customers c3LEFT JOIN LATERAL (4  SELECT id, amount5  FROM orders6  WHERE customer_id = c.id7  ORDER BY amount DESC, id8  LIMIT 29) o ON true10ORDER BY c.id, o.amount DESC, o.id;

LEFT JOIN LATERAL (...) ON true keeps customers for whom the subquery returns nothing (Luka); CROSS JOIN LATERAL would drop them. ON true is the idiom because the correlation already happens inside the subquery. The ORDER BY amount DESC, id inside makes the result deterministic when two orders share an amount: without the id, which of Nino's equal 120.00 orders comes first is up to the executor.

DISTINCT ON: one row per key, chosen by ORDER BY

SELECT DISTINCT ON (key) ... keeps the first row of each group of equal key values, where "first" is defined by the query's ORDER BY. The ORDER BY must start with the same expressions as DISTINCT ON; what follows decides which row wins.

1CREATE TABLE status_log (2  id         int PRIMARY KEY,3  ticket_id  int NOT NULL,4  status     text NOT NULL,5  changed_at timestamp NOT NULL6);7INSERT INTO status_log VALUES8  (1, 100, 'open',        '2024-05-01 09:00'),9  (2, 100, 'in_progress', '2024-05-02 10:00'),10  (3, 200, 'open',        '2024-05-01 11:00'),11  (4, 100, 'closed',      '2024-05-03 08:00'),12  (5, 200, 'closed',      '2024-05-01 11:00');

1SELECT DISTINCT ON (ticket_id)2       ticket_id, status, changed_at::text AS changed_at3FROM status_log4ORDER BY ticket_id, changed_at DESC, id DESC;

Ticket 200 has two changes at the same instant. changed_at DESC alone would leave the winner unspecified; adding id DESC makes the later insert win, every time. DISTINCT ON is PostgreSQL-specific; the portable equivalent is row_number() in a subquery, covered with window functions.

Semi-joins and anti-joins

A join returns one row per matching pair. When the question is only "does a match exist?", that multiplies rows, and a DISTINCT added to hide it costs a sort or hash over the whole result. EXISTS is a semi-join: it returns each left row at most once and can stop at the first match. NOT EXISTS is the anti-join, and unlike NOT IN it is safe with NULLs.

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount numeric(10,2) NOT NULL);3INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');4INSERT INTO orders VALUES5  (10, 1, 50), (11, 1, 120), (12, 1, 80), (13, 2, 30), (14, 2, 30), (15, 1, 120);

1SELECT 'join' AS how, count(*) AS rows_returned2FROM customers c3JOIN orders o ON o.customer_id = c.id4WHERE o.amount >= 505UNION ALL6SELECT 'exists', count(*)7FROM customers c8WHERE EXISTS (9  SELECT 1 FROM orders o10  WHERE o.customer_id = c.id AND o.amount >= 5011);

Common mistakes

• Writing JOIN (SELECT ... LIMIT 2) without LATERAL and getting two rows in total instead of two per customer.
• Using CROSS JOIN LATERAL where customers with no rows must still appear.
• DISTINCT ON with an ORDER BY that does not fully order each group: the "latest" row changes between runs when timestamps tie.
• Joining to a child table to filter parents, then adding DISTINCT to undo the duplicates, instead of writing EXISTS.

What interviewers ask

• How do you get the latest order per customer in PostgreSQL? DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC, id DESC, or row_number(); mention the tie-break.
• What does LATERAL allow that a plain subquery does not? Referencing columns from earlier FROM items, so the subquery runs per row; used for top-N per group and set-returning functions.
• EXISTS versus IN versus JOIN? EXISTS is a semi-join and never duplicates; NOT EXISTS avoids the NOT IN NULL trap.