Every SQL Join Type in 10 Minutes

You joined two tables, and a customer just vanished from your report. Ana runs a small tea shop with four customers, and she wants each one next to the tea they ordered. Her query comes back with four rows. But Ben isn't there, and Mia shows up twice.

shop=# SELECT name, tea FROM customers c JOIN orders o ON o.customer_id = c.id; name |    tea
------+-----------
 Mia  | Sencha
 Mia  | Assam
 Leo  | Earl Grey
 Sara | Sencha
(4 rows)

Nothing is broken. The join did exactly what that kind of join does. There are seven kinds of join you'll meet in SQL, and each one decides differently which rows survive. By the end you'll know all seven, and why one popular way to find customers who never ordered returns nothing at all.

A question on Stack Overflow asks exactly this: what's the difference between an inner join and an outer join? Asked in 2008, it's been viewed more than two point seven million times. Here's the whole answer, run for real.

First, the two tables. Customers: Mia, Leo, Sara and Ben, each with an id. Orders: five of them. Each order keeps the id of the customer who placed it. Order 105 has none: NULL, meaning no value, a guest who checked out without an account.

shop=# SELECT * FROM customers; id | name | referred_by
----+------+-------------
  1 | Mia  |        NULL
  2 | Leo  |           1
  3 | Sara |           1
  4 | Ben  |           2
(4 rows)

shop=# SELECT * FROM orders; id  | customer_id |    tea
-----+-------------+-----------
 101 |           1 | Sencha
 102 |           1 | Assam
 103 |           2 | Earl Grey
 104 |           3 | Sencha
 105 |        NULL | Oolong
(5 rows)

An inner join pairs rows that match. For each order, it finds the customer whose id equals the order's customer id. Mia placed two orders, so she appears twice. Leo once, Sara once. Ben has no orders, and the guest order has no customer. With nothing to pair with, both drop out. That is also how a join multiplies rows: one customer with two orders becomes two rows.

The join itself is one line. Join, on its own, means inner join. Then the second table, orders, nicknamed o. And after on, the rule that decides a match: the order's customer id equals the customer's id.

Real systems are built on this same shape. GitLab runs on PostgreSQL, and its own developer guide joins the projects table to the namespaces table, the users and groups that own each project, with exactly this kind of match rule.

But Ana wanted every customer, whether they ordered or not. That's a left join. One word changed. 5 rows now. Ben is back, and where his tea would be, PostgreSQL fills in NULL.

shop=# SELECT name, tea FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; name |    tea
------+-----------
 Mia  | Sencha
 Mia  | Assam
 Leo  | Earl Grey
 Sara | Sencha
 Ben  | NULL
(5 rows)

A left join keeps every row of the left table, the one named first, and adds matches from the right when there are any. No match? The row stays anyway, with NULLs in the right side's columns. The guest order sits on the right, so it still drops out.

Those NULLs matter for everything that follows. NULL isn't zero, and it isn't empty text. It means no value at all: unknown. SQL can't say whether an unknown value equals anything, not even another NULL, so any comparison with NULL is never true.

Here's the trap that catches almost everyone. Ana wants every customer, plus any Sencha they bought, so she adds where tea equals Sencha. Two rows. Leo and Ben are gone again. Now she moves that condition into the on part instead. All four customers are back, with Sencha where they bought it.

shop=# SELECT name, tea FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE tea = 'Sencha'; name |  tea
------+--------
 Mia  | Sencha
 Sara | Sencha
(2 rows)

shop=# SELECT name, tea FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND tea = 'Sencha'; name |  tea
------+--------
 Mia  | Sencha
 Leo  | NULL
 Sara | Sencha
 Ben  | NULL
(4 rows)

Why? PostgreSQL's documentation says it plainly: a condition in on is applied before the join, where is applied after it. After the left join, Ben's tea is NULL, and NULL never equals Sencha, so where throws his row away. The left join quietly turned back into an inner join.

What about the guest order? A right join is the mirror image: it keeps every row of the right table, the one named second. Now the Oolong order is in, with NULL for the name, and Ben is out. Swap the two tables, use a left join, and you get the same five rows.

shop=# SELECT name, tea FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id; name |    tea
------+-----------
 Mia  | Sencha
 Mia  | Assam
 Leo  | Earl Grey
 Sara | Sencha
 NULL | Oolong
(5 rows)

shop=# SELECT name, tea FROM orders o LEFT JOIN customers c ON o.customer_id = c.id; name |    tea
------+-----------
 Mia  | Sencha
 Mia  | Assam
 Leo  | Earl Grey
 Sara | Sencha
 NULL | Oolong
(5 rows)

So a right join keeps every order, matched or not. Many people rarely write one. They put the table they want to keep first, and use a left join, because a query reads more easily when the table you keep comes first. Both give the same rows; pick one style and stick to it.

Now keep everyone from both sides. That's a full outer join. Customers has four rows, orders has five. Will it add them up? Guess first: how many rows will it return? Six. The four matched pairs, Ben with no order, and the guest order with no customer.

Here it is in PostgreSQL. Every row from both tables, at least once, with NULL wherever the other side had nothing. A full join is how you line up two lists, like orders against payments: the NULLs show what's missing on each side.

shop=# SELECT name, tea FROM customers c FULL JOIN orders o ON o.customer_id = c.id; name |    tea
------+-----------
 Mia  | Sencha
 Mia  | Assam
 Leo  | Earl Grey
 Sara | Sencha
 NULL | Oolong
 Ben  | NULL
(6 rows)

A cross join has no match rule at all. Every row on one side meets every row on the other. Ana uses one on purpose, to build her menu: four teas, three cup sizes. Every tea comes in every size. Guess first: how many menu lines? Twelve: four times three. PostgreSQL's documentation puts it the same way: N rows times M rows.

In SQL it's cross join, and out come 12 rows, every tea in every size. The same thing happens by accident when a query lists two tables with a comma and forgets the match rule. Four customers times five orders: 20 rows, and no error. With a thousand customers and ten thousand orders, that's ten million.

shop=# SELECT tea, size FROM teas CROSS JOIN sizes;    tea    |  size
-----------+--------
 Sencha    | small
 Sencha    | medium
 Sencha    | large
 Assam     | small
 Assam     | medium
 Assam     | large
 Earl Grey | small
 Earl Grey | medium
 Earl Grey | large
 Oolong    | small
 Oolong    | medium
 Oolong    | large
(12 rows)

shop=# SELECT count(*) FROM customers, orders; count
-------
    20
(1 row)

A self join isn't a new keyword: it's a table joined to itself. Ana's customers table has a referred by column: the id of the customer who told them about the shop. Leo came through Mia, and so did Sara. Ben came through Leo. To turn that id into a name, the query reads the same table twice, under two nicknames. You'll meet this shape anywhere a row points at another row of the same table: an employee and their manager, a reply and the comment it answers.

c is the customer, r is the referrer, and the rule pairs them when r's id equals c's referred by. Three rows. Mia found the shop on her own, so this inner self join leaves her out; a left self join would keep her, with NULL.

shop=# SELECT c.name, r.name AS referred_by FROM customers c JOIN customers r ON r.id = c.referred_by; name | referred_by
------+-------------
 Leo  | Mia
 Sara | Mia
 Ben  | Leo
(3 rows)

The last one turns Ana's question around: which customers have never ordered? That's an anti join: keep the rows on the left that have no match on the right. This is the list Ana would send a welcome discount to. SQL has no anti join keyword, so you write it one of a few ways.

One: a left join, keeping only the rows where the order side came back as NULL. That leaves Ben. Two: not exists, keep a customer when no order points at them. Again, it's Ben.

shop=# SELECT name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL; name
------
 Ben
(1 row)

shop=# SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); name
------
 Ben
(1 row)

Flip it, exists instead of not exists, and you get its opposite, a semi join: the customers who did order, each one listed once. Mia isn't doubled, the way the inner join doubled her at the start.

And three, the popular one from the start: not in. Customers whose id is not in the list of customer ids on orders. Zero rows. Not even Ben.

shop=# SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders); name
------
(0 rows)

The guest order put a NULL into that list. To keep Ben, SQL must be sure his id, four, differs from every value in it. One, two, three: different. But is four different from NULL, an unknown value? SQL can't say. PostgreSQL's documentation spells it out: with a NULL in the list, not in is never true. Use not exists instead.

Seven joins, one question: which rows do you keep? Inner keeps matches. Left, right and full keep unmatched rows from one side or both, with NULLs. Cross keeps every pair, self reads one table twice, and anti keeps what has no match.

Every query and the lab files are linked in the description, to run yourself. If seeing all seven run for real helped, a like helps YouTube find other beginners stuck on joins, and subscribing is free.

Every query here ran for real on PostgreSQL 18. Thanks for watching Cloud Explainers.

Sources

• PostgreSQL 18 documentation, joined tables (cross, inner, left, right and full outer joins; a restriction in ON is processed before the join, one in WHERE after it)
• PostgreSQL 18 documentation, NOT IN (with a null in the subquery, the result is null, not true)
• GitLab SQL query guidelines (projects joined to namespaces)
• Stack Overflow: What is the difference between INNER JOIN and OUTER JOIN? (asked 2008)