Joins: inner, outer, semi, anti and self

conceptThe concept

INNER JOIN keeps only pairs that match. LEFT JOIN keeps every row on the left and fills the right side with NULLs when nothing matches; RIGHT is the mirror image and FULL keeps unmatched rows from both sides. CROSS JOIN is every combination.

The condition in ON decides which rows match; the condition in WHERE decides which rows survive. With an outer join they are not interchangeable: a WHERE filter on a right-side column throws away the NULL-filled rows and turns the LEFT JOIN back into an inner join.

A semi-join asks "does a partner exist?" without multiplying rows (EXISTS, IN). An anti-join asks "is there no partner?" (NOT EXISTS, or LEFT JOIN ... WHERE right.key IS NULL). A self-join joins a table to itself under two aliases, for example an employee and their manager.

whyWhy it matters

Join mistakes do not raise errors; they return plausible numbers. A one-to-many join before a SUM double-counts. A filter in the wrong clause drops customers who never ordered. Knowing the join semantics is how you notice.

Use caseReal-world scenario

Marketing asks for every customer in Berlin with their number of orders, including people who have never ordered. That is a LEFT JOIN plus a count of a right-side column, not count(*).

Try itExamples

Run and edit these in the interactive lesson.

SELECT o.id, o.created_at::date AS day, c.name, c.city, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 5;

SELECT c.id, c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Berlin'
GROUP BY c.id, c.name
ORDER BY orders, c.id
LIMIT 8;

Both queries try to list every Berlin customer with their cancelled orders. Compare the row counts.

SELECT count(*) AS where_filter
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Berlin' AND o.status = 'cancelled';

SELECT count(*) AS on_filter
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
WHERE c.city = 'Berlin';

-- customers who reviewed something
SELECT count(*) AS reviewers
FROM customers c
WHERE EXISTS (SELECT 1 FROM reviews r WHERE r.customer_id = c.id);

-- products that were never ordered
SELECT p.sku, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id)
ORDER BY p.sku;

SELECT e.name AS employee, e.title, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
WHERE e.dept = 'Engineering'
ORDER BY e.id;

USING joins on same-named columns and outputs the column once. NATURAL joins on every column name the tables share, which breaks the day someone adds a column called created_at to both tables.

SELECT order_id, count(*) AS lines
FROM order_items
JOIN (SELECT id AS order_id FROM orders WHERE status = 'pending' LIMIT 3) p USING (order_id)
GROUP BY order_id
ORDER BY order_id;

internalsUnder the hood

The planner recognises EXISTS and NOT EXISTS as semi- and anti-joins and can execute them with a hash, merge or nested-loop algorithm; you will see Hash Semi Join or Hash Anti Join in plans. The performance level covers how it chooses.

Avoid / preferDo this, not that

SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

SELECT * FROM orders, customers WHERE orders.total > 100;

SELECT * FROM orders JOIN customers ON customers.id = orders.customer_id
WHERE orders.total > 100;

PitfallsCommon mistakes

• Filtering a right-side column of a LEFT JOIN in WHERE. Move the condition into ON, or test IS NULL deliberately.
• Summing a parent column after joining to a child table (for example sum(o.total) after joining order_items). Each order is counted once per line item. Aggregate the child first, then join.
• Using count(*) with a LEFT JOIN to count children. It counts the NULL-filled row as 1.

LimitsWhen not to use it

Avoid NATURAL JOIN outside throwaway queries: it joins on every column with the same name, so adding an unrelated created_at column to both tables silently changes the result.

SecuritySecurity notes

A view runs with its owner's privileges by default, so a view that joins a sensitive table can expose it to roles without access to that table. Use security_invoker = true on views when callers' privileges and row-level security should apply.

ChallengeTry it yourself

SELECT count(*) AS reviews_without_purchase
FROM reviews r
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  WHERE o.customer_id = r.customer_id AND i.product_id = r.product_id
);

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Joins between tables
• Table expressions