Inner and outer joins · Joins and relational querying

Inner and outer joins

ConceptFoundation4 min read15 min hands-on#fundamentals#sql

The idea in 30 seconds

A join combines rows from two tables that match a condition. An inner join keeps only the matches; a left join also keeps every row from the left table, with NULLs where nothing matched. Where you put a condition matters: on the right table, a filter in WHERE silently turns a left join back into an inner join.

Helps to know first: SQL basics: reading data, Relational database concepts

Inner join: only the matches

orders stores a customer_id. To show each order with its customer’s name, join the two tables on that column:

SELECT o.id AS order_id, c.name, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

 order_id |    name    |  status
----------+------------+-----------
      101 | Asha Rao   | shipped
      102 | Ben Okafor | paid
      103 | Asha Rao   | shipped
      104 | Chen Wei   | cancelled
      …
(12 rows)

For every order, PostgreSQL finds the customer rows that satisfy c.id = o.customer_id and outputs one combined row per match. o and c are aliases: short names for the tables, needed whenever both tables have a column with the same name, such as id.

JOIN on its own means INNER JOIN. It keeps only rows that match. Elena and Hugo have no orders, so they appear nowhere in this result.

Left join: keep every row on the left

To list every customer with their orders, including customers with none, use a LEFT JOIN:

SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

     name      | order_id |  status
---------------+----------+-----------
 Asha Rao      |      101 | shipped
 Asha Rao      |      103 | shipped
 Asha Rao      |      108 | paid
 Ben Okafor    |      102 | paid
 …
 Elena Petrova |          |
 …
 Hugo Martin   |          |
(14 rows)

Every customer appears at least once. Where nothing matched, the order columns are NULL. Notice the row counts: 12 for the inner join, 14 for the left join, the extra two being Elena and Hugo.

Join	Keeps
`JOIN` (inner)	Only matching pairs
`LEFT JOIN`	Every row of the left table, plus matches
`RIGHT JOIN`	Every row of the right table, plus matches (a left join written the other way round)
`FULL JOIN`	Every row of both tables, matched where possible
`CROSS JOIN`	Every combination: 8 customers × 11 products = 88 rows

Joining more than two tables

Joins chain. Asha’s orders, with the product names inside them:

SELECT o.id AS order_id, p.name AS product, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.customer_id = 1
ORDER BY o.id, p.name;

 order_id |           product           | quantity | unit_price
----------+-----------------------------+----------+------------
      101 | Mechanical keyboard         |        1 |    4500.00
      101 | Wireless mouse              |        1 |    1200.00
      103 | Laptop stand                |        1 |    1899.00
      103 | USB-C hub                   |        1 |    2499.00
      103 | Wireless mouse              |        1 |    1100.00
      108 | Noise-cancelling headphones |        1 |   11999.00

ON versus WHERE in a left join

PredictEvery customer, with their shipped orders

You want every customer listed, with their shipped orders if they have any. You write:

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

How many customers appear?

Six, not eight. Ben, Elena and Hugo are missing; Ben has orders, but none shipped.

The LEFT JOIN did keep everyone, with NULL order columns for those without a match. Then WHERE ran: for those rows o.status is NULL, so o.status = 'shipped' is unknown, and WHERE drops them. The result is the same as an inner join.

Put the condition in ON instead, so it decides which orders match rather than which rows survive:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'
ORDER BY c.id, o.id;

That returns nine rows covering all eight customers, with an empty order_id for Ben, Elena and Hugo.

Check yourself

What is the difference between JOIN and LEFT JOIN?
Show answer
JOIN (inner) keeps only rows that have a match in both tables. LEFT JOIN keeps every row of the left table, filling the right table’s columns with NULL where nothing matched.
I could explain this without looking
Why does a condition on the right table in WHERE cancel out a LEFT JOIN?
Show answer
For left rows with no match, the right table’s columns are NULL. A comparison with NULL is unknown, so WHERE discards those rows: exactly the rows the left join was meant to keep.
I could explain this without looking
How many rows does customers CROSS JOIN products return, and why?
Show answer
88: every one of the 8 customers combined with every one of the 11 products.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 7.2 Table Expressions

Primary· §7.2.1.1 Joined Tables

Definitions of inner, left, right, full and cross joins; a left join always has at least one row for each row of the left table, with nulls for the right table's columns where nothing matched; a cross join of N and M rows has N × M rows.