JOINs: Combining Tables

Data in a relational database is deliberately spread across multiple tables. Customers live in one table. Orders live in another. Products live in a third. This separation eliminates redundancy and makes updates reliable. But it means that to answer most real questions, you need to combine tables. JOINs are the mechanism for doing that.

The fundamental idea is simple: a JOIN takes rows from two tables and combines them based on a condition. The type of JOIN determines what happens when a row in one table has no matching row in the other.

INNER JOIN

An INNER JOIN returns only rows where the join condition is satisfied in both tables:

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

If an order has a customer_id that does not exist in the customers table, that order is excluded. If a customer has no orders, they are excluded too. Only rows with a match on both sides appear in the result.

The keyword INNER is optional. A plain JOIN is an inner join.

LEFT JOIN

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. When no match exists, the right-side columns are filled with NULL.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

This includes every customer, even those who have never placed an order. For those customers, o.id is NULL, so COUNT(o.id) returns 0.

Use a LEFT JOIN when you want all rows from one table regardless of whether matching rows exist in the other. It is especially useful for "find rows in A that have no corresponding row in B":

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;  -- customers with no orders at all

RIGHT JOIN

The mirror image of a LEFT JOIN: all rows from the right table, with matching rows from the left. In practice, you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table positions, and most people do. The two are logically equivalent.

FULL JOIN

Returns all rows from both tables, with NULLs on whichever side has no match:

SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL JOIN table_b b ON a.id = b.id;

This is useful for comparing two tables and finding rows that exist in one but not the other.

Comparing join types

Join type	Returns
INNER JOIN	Only rows with a match in both tables
LEFT JOIN	All rows from the left table; NULLs for unmatched right-side columns
RIGHT JOIN	All rows from the right table; NULLs for unmatched left-side columns
FULL JOIN	All rows from both tables; NULLs where no match exists

The ON clause vs WHERE for filtering

You can add extra conditions to a join in the ON clause or in WHERE. For an inner join, the result is the same. For a LEFT JOIN, the behavior differs in an important way.

-- Filtering in ON: all customers appear; only shipped orders are shown
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'

-- Filtering in WHERE: customers with no shipped orders are excluded
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'

The ON condition is part of the join logic. The WHERE condition filters after the join is assembled. For left joins especially, this distinction determines whether unmatched rows survive into the result.

Table aliases

When joining multiple tables, aliases make queries dramatically more readable and prevent ambiguity when two tables share column names:

SELECT o.id, c.name, p.title, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Without aliases, you would have to write orders.id, customers.name, and so on throughout the query. Aliases keep it clean and easy to scan.