LEFT & RIGHT JOIN

An inner join drops rows that have no match. Often that's exactly the information you need: customers who've never ordered, products that never sold, departments without staff. Outer joins keep unmatched rows and fill the missing side with NULL.

LEFT JOIN

SELECT c.name, o.id AS order_id, o.order_date
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'Portugal'
ORDER BY c.name, o.id;

LEFT JOIN keeps every row from the left table (the one before the keyword). Julia has no orders, but she's still listed, with NULL for the order columns. (LEFT OUTER JOIN means the same; OUTER is optional.)

Finding rows with no match

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

Keep all customers, then keep only those where no order was found. This "anti-join" pattern answers questions like "who hasn't…?".

SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

The Smart Plug has never been ordered.

Counting with outer joins

SELECT d.name, COUNT(e.id) AS staff
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id
GROUP BY d.name
ORDER BY staff DESC;

Research appears with 0 staff. Note COUNT(e.id), not COUNT(*): the unmatched row still exists, so COUNT(*) would count it as 1.

ON vs WHERE in outer joins

SELECT c.name, o.id AS pending_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'pending'
WHERE c.country = 'UK';

A condition in ON limits which rows are matched; all customers are still kept. Move o.status = 'pending' to WHERE and Ben disappears, because his rows have a NULL status after the join and WHERE removes them:

SELECT c.name, o.id AS pending_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.status = 'pending';

RIGHT JOIN

SELECT e.first_name, d.name AS department
FROM employees AS e
RIGHT JOIN departments AS d ON d.id = e.department_id
WHERE e.id IS NULL OR e.id <= 3;

RIGHT JOIN keeps every row from the right table. It's a mirror image of LEFT JOIN — A RIGHT JOIN B equals B LEFT JOIN A — so most people swap the tables and use LEFT JOIN consistently. SQLite supports RIGHT and FULL joins since version 3.39.

Defaults for missing values

SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE p.category = 'Electronics'
GROUP BY p.name
ORDER BY units_sold DESC;

Try it yourself

List every employee with their department name, including anyone without a department.