Relational databases split data into tables — customers in one, orders in another — and joins combine them in queries. Once you understand the handful of join types, most “how do I get this report?” questions answer themselves.

Example data

customers                      orders
id | name                      id | customer_id | total
---+--------                   ---+-------------+------
 1 | Alice                    10 | 1           | 50
 2 | Bob                      11 | 1           | 20
 3 | Carol                    12 | 2           | 75
                              13 | 9           | 30     ← customer 9 doesn't exist

INNER JOIN — only matching rows

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
Alice | 10 | 50
Alice | 11 | 20
Bob   | 12 | 75

Carol (no orders) and order 13 (no customer) are excluded. JOIN alone means INNER JOIN.

LEFT JOIN — all rows from the left table

SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
Alice | 10   | 50
Alice | 11   | 20
Bob   | 12   | 75
Carol | NULL | NULL

Every customer appears; missing order columns are NULL. The most common join for reports like “all customers and their order totals”.

RIGHT JOIN — all rows from the right table

The mirror image: all orders, with customer columns NULL where there’s no match (order 13). Usually written as a LEFT JOIN with the tables swapped, which reads more naturally.

FULL OUTER JOIN — everything

All customers and all orders, matched where possible. Supported by PostgreSQL; MySQL doesn’t support FULL OUTER JOIN — emulate it with a LEFT JOIN … UNION … RIGHT JOIN.

Anti-join: rows without a match

Customers who never ordered:

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

Or with NOT EXISTS (often clearer, and well optimised):

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Avoid NOT IN (subquery) when the subquery can return NULL — it then returns no rows at all, a classic surprise.

Aggregating across a join

Order count and revenue per customer, including customers with none:

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Use COUNT(o.id), not COUNT(*), so customers without orders count 0, not 1.

CROSS JOIN — every combination

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

Produces rows × rows — useful for generating combinations, dangerous by accident (a missing ON clause in older comma syntax does the same).

SELF JOIN — a table joined to itself

Employees and their managers in one table:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Common mistakes

Filtering a LEFT JOIN in WHERE turns it back into an inner join:

-- Wrong: drops customers with no paid orders
SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- Right: filter in the ON clause
SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

Row multiplication — joining two one-to-many relations at once (orders and payments for each customer) multiplies rows and inflates sums. Aggregate in subqueries first, then join.

Ambiguous columns — always use table aliases (c.id, o.id).

Joins and performance

Index the join columns — especially foreign keys like orders.customer_id. Without an index, the database may scan the whole table for each row. PostgreSQL doesn’t create foreign key indexes automatically. Check plans with EXPLAIN — see database indexing basics and MySQL EXPLAIN.

Related: MySQL vs PostgreSQL.

  • SQL
  • Databases
  • MySQL