SQL JOIN Types Explained With Examples

A wrong JOIN doesn’t throw an error. It runs, returns a result set, and the rows are wrong: customers missing, totals inflated, orphaned records hidden. Most of the time the query asked for exactly what it got, and the author picked the wrong join type or filtered in the wrong place.

A join combines rows from two tables on a condition you write. Which rows survive depends on the join type, and the six types below give six different answers from the same two tables.

customers holds five people. Three of them were referred by another customer, recorded in referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders holds six orders. Five point at a real customer. The sixth, 106, points at customer 99, who was deleted after placing it, so the order no longer matches anyone:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

The quickest way to follow along is a Postgres container, set up the same way as in What Is Docker Compose and How to Use It. The joins are standard SQL. The two places where MySQL differs are called out below.

INNER JOIN: only the rows that match on both sides

1
2
3
4

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

Five rows. Carol and Erin have no orders, so they’re gone. Order 106 has no customer, so it’s gone too. JOIN and INNER JOIN mean the same thing. INNER is optional and most people leave it out.

On five rows nothing here is slow. On a real orders table, orders.customer_id needs an index, or every join against it scans the whole table. How Database Indexes Work and When to Add One covers when that index pays off.

LEFT JOIN: every row on the left, matched or not

1
2
3
4

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

Every customer is there. Carol and Erin get NULL in the order columns. This is the join you’ll write most often: “all of X, with whatever Y they have.”

RIGHT JOIN: every row on the right, matched or not

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Order 106 is back, with a NULL name. This is the orphaned row the inner join hid, and a right join (or the equivalent left join) is how you find it on purpose.

You’ll rarely see RIGHT JOIN in real code. a RIGHT JOIN b returns the same rows as b LEFT JOIN a, so most teams use LEFT JOIN everywhere and change the table order instead. Rewriting a RIGHT JOIN that way is about consistency. The original is not wrong.

FULL OUTER JOIN: everything from both sides

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Eight rows: every match, plus the unmatched customers, plus the unmatched order. That makes it the join for reconciliation, where you want everything on either side that doesn’t line up. Outside that, you won’t need it much.

MySQL has no FULL OUTER JOIN, and it doesn’t accept NULLS LAST either. To get the same rows there, take the LEFT JOIN, then UNION ALL a RIGHT JOIN filtered with WHERE c.customer_id IS NULL so the matched rows aren’t repeated. A plain UNION of the two joins also works, but it pays for a deduplication pass and also merges rows that really are identical.

CROSS JOIN: every row paired with every row

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Five customers times six orders: thirty rows, with no join condition at all. Most cross joins in production are accidents. Someone wrote FROM customers, orders and forgot the WHERE that was supposed to connect them. You’d want one on purpose to generate combinations, like every size with every color, or every day in a date range with every store, before filling in the real figures.

How does a self-join work?

customers.referred_by points at another row in the same table. To show who referred whom by name, join the table to itself under a second alias:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

The join itself works the same way as before. customers c and customers r are two aliases, and the planner treats them as two separate tables that happen to hold the same rows. It’s a LEFT JOIN so that Alice and Bob, who weren’t referred, stay in the result. Any table where a row points at another row in the same table works this way: employees and managers, categories and parent categories, comments and replies.

Why does a WHERE clause turn a LEFT JOIN into an INNER JOIN?

You want every customer, with their orders over $25 where they have any:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol and Erin are gone. WHERE runs after the join, and for them o.amount is NULL. NULL > 25 is neither true nor false. It’s unknown, and WHERE keeps only rows that are true. The LEFT JOIN kept them and the filter threw them out.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Keep every customer; only filter which of their orders count
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Keeps customers with no orders at all, but not customers whose orders are all <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Use the first one. Putting the condition in ON decides which orders are allowed to match, and every customer still gets at least one row. The second version only lets through rows where the join found nothing. A customer whose only order was $20 gets a row with amount = 20.00, which fails both conditions, and that customer disappears again. On this data both queries happen to keep all five customers, which is how the second one gets through testing.

Why does SUM return the wrong total after joining two tables?

Add a third table, payments, where one order can be paid in several installments:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

Order 101 is $50, paid in two $25 installments. Now join all three tables and total what each customer ordered:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

Alice’s real total is $70 (50.00 + 20.00). Two things went wrong. Order 101 matches two payments, so the join returns that order row twice and SUM(o.amount) adds its $50 twice. Order 102 has no payments, so the inner join to payments drops it. For Alice that gives 50 + 50 + 0 = 100. Dave has no payments at all, so he isn’t in the result.

The rule: when you aggregate a column, don’t join tables the aggregate doesn’t need. Every one-to-many join can multiply rows or remove them. If you only want order totals, leave payments out:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

If you need payment totals in the same report, GROUP BY order_id on payments in a subquery or CTE first. That gives one row per order, and you can join it to the orders safely.

NOT IN vs NOT EXISTS: how do you find rows with no match?

“Customers with no orders” looks like a job for NOT IN:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

It works here only because orders.customer_id is NOT NULL. Drop that constraint and insert a single order with a NULL customer, and the query returns zero rows, with no error. NOT IN expands to a chain of <> comparisons joined by AND. x <> NULL is unknown, so one NULL makes the whole condition unknown for every customer.

Two patterns don’t have this problem:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

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

Both return Carol and Erin, and a NULL in orders.customer_id doesn’t affect either one. Neither builds a list to compare against. Both check, customer by customer, whether a matching order exists. Use NOT EXISTS by default. Use LEFT JOIN ... IS NULL when the query already joins the two tables to get other columns.

When a join returns more rows than you expect, look for a one-to-many table between the join and the GROUP BY. When it returns fewer, look for a WHERE condition on the table that’s allowed to be NULL. In both cases, fix the query around the join before you change the join type.

Related articles

• How Database Indexes Work and When to Add One
• What Is Docker Compose and How to Use It
• How Redis Caching Works and How to Use It