SQL JOIN Types Explained (With Examples)

Published 2026-08-27 · 8 min read

Every JOIN answers the same question — "how do I combine rows from two tables?" — but each type answers it with a different rule about what to do when a row on one side has no match on the other. Here's the running example we'll use throughout:

customers                  orders
+----+---------+           +----+-------------+--------+
| id | name    |           | id | customer_id | total  |
+----+---------+           +----+-------------+--------+
| 1  | Ada     |           | 1  | 1           | 42.00  |
| 2  | Alan    |           | 2  | 1           | 15.50  |
| 3  | Grace   |           | 3  | 3           | 99.99  |
+----+---------+           +----+-------------+--------+

Note that Alan (id 2) has no orders, and order 3 belongs to a customer not shown, if we imagine a fourth customer_id = 4 row. We'll come back to both gaps.

INNER JOIN — only the matches

Returns rows where the join condition matches on both sides. Customers with no orders are dropped entirely.

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

-- name | total
-- Ada  | 42.00
-- Ada  | 15.50

Grace has no matching order row here since we omitted her order for this example, and Alan is excluded because he has no orders at all.

LEFT JOIN — everything on the left, matched or not

Returns every row from the left table. Where there's no match on the right, the right table's columns come back as NULL.

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

-- name  | total
-- Ada   | 42.00
-- Ada   | 15.50
-- Alan  | NULL
-- Grace | NULL

This is the join to reach for when you want "all customers, with their orders if any" — finding customers who've never ordered is a one-line filter away: add WHERE o.id IS NULL.

RIGHT JOIN — the mirror image

Returns every row from the right table, filling in NULL for the left side when there's no match. It's used less often than LEFT JOIN simply because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.

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

FULL JOIN (FULL OUTER JOIN) — everything, matched or not, from both sides

Combines LEFT and RIGHT: every row from both tables, with NULLs filled in on whichever side is missing a match. Useful for finding mismatches in both directions at once — customers with no orders, and orders pointing at customers that don't exist.

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

CROSS JOIN — every combination, no condition

Pairs every row on the left with every row on the right — no matching column at all. Three customers CROSS JOINed with three orders returns nine rows. It's rarely what you want by accident (a missing ON clause silently becomes a CROSS JOIN in some dialects), but it's genuinely useful for generating combinations — every product with every size, for example.

SELECT c.name, o.total
FROM customers c
CROSS JOIN orders o;
-- 3 customers × 3 orders = 9 rows

Picking the right one

Testing joins is easier with a schema you can actually see. QuerySQL's schema diagram lays out foreign-key relationships visually, so it's obvious which column joins to which before you write the query.