29 INNER and LEFT JOIN

Joins combine rows from two (or more) tables based on a matching condition. They are how relational data is rebuilt into the shape your application needs. INNER JOIN keeps only rows where the condition matches on both sides; LEFT JOIN (also called LEFT OUTER JOIN) keeps every row from the left side and fills in NULL where the right side has no match. Mastering joins is mastering SQL.

What you’ll learn

• INNER JOIN with ON: keep matched rows only
• LEFT JOIN: keep everything on the left, NULL on the right when unmatched
• Table aliases (c, o) for readability
• Filtering on the JOIN target vs. in the WHERE clause
• USING (col) as a shorthand for ON a.col = b.col

Example

-- Two small tables for joins
DROP TABLE IF EXISTS orders_example;
DROP TABLE IF EXISTS customers_example;

CREATE TABLE customers_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders_example (
  id          integer PRIMARY KEY,
  customer_id integer,                      -- intentionally not a real FK so we can show unmatched rows
  total       numeric(10, 2) NOT NULL
);

INSERT INTO customers_example VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders_example   VALUES (1, 1, 50), (2, 1, 30), (3, 99, 10);  -- order 3 points at a non-existent customer

-- INNER JOIN: only rows that match on both sides
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
INNER JOIN orders_example o ON o.customer_id = c.id
ORDER BY o.id;

-- LEFT JOIN: every customer, even those without orders
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
ORDER BY c.id, o.id;

-- Filtering on the JOIN vs in WHERE matters for LEFT JOIN.
-- "Customers and their orders > 40, even if they have none":
SELECT c.name, o.id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id AND o.total > 40
ORDER BY c.id;

-- vs "customers who have orders > 40" — the WHERE turns the LEFT into an INNER:
SELECT c.name, o.id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
WHERE o.total > 40
ORDER BY c.id;

The ON clause is the join condition. It can be any boolean expression, though usually it is parent.id = child.parent_id. Conditions in ON are part of the join itself — for a LEFT JOIN, a row from the left side survives even if the right side has no matching row that satisfies ON. Conditions in WHERE are applied after the join, so a WHERE child.col = 'x' on a LEFT JOIN filters out unmatched (NULL) rows — turning the join effectively into an INNER JOIN.

USING (col) is shorthand when the columns are named the same on both sides: USING (customer_id) is equivalent to ON a.customer_id = b.customer_id, and the resulting row has just one customer_id column instead of two.

Order 3 has customer_id = 99 (no customer with that id). INNER JOIN drops it; LEFT JOIN keeps customers without orders (Bob, Carol). The two together give the full picture.

Run the example

To run:

$ psql -f source/inner-and-left-join.sql postgres
...
 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
(2 rows)

 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
 Bob   |          |
 Carol |          |
(4 rows)
 ...

Common pitfalls

• WHERE right_table.col = 'x' on a LEFT JOIN accidentally drops the unmatched rows you wanted to keep. Put the condition in ON if it should be part of the match, in WHERE if you want to filter after the join.
• Joining on a column without an index: the planner falls back to a hash join or merge join that may scan the entire table. Index your foreign-key columns.
• Cartesian products: CROSS JOIN or a comma-separated FROM list without a connecting predicate produces m × n rows. A plain INNER JOIN requires ON or USING; PostgreSQL does not warn about a valid cross join.
• LEFT JOIN ... GROUP BY is the natural shape for “count of children per parent, including parents with zero.” Use count(child.id) (not count(*)) so unmatched rows yield 0.

Downloads

SQL file: inner-and-left-join.sql · All lesson files · Setup guide