SQL Joins Explained with Examples
4 min read · Published
Table of contents
A join combines rows from two tables based on a related column. Joins are how relational databases let you keep data normalised (customers in one table, orders in another) yet query it together. This guide uses two tiny tables to show each join type and its result.
The sample data
customers
| id | name |
|---|---|
| 1 | Ada |
| 2 | Grace |
| 3 | Linus |
orders
| id | customer_id | total |
|---|---|---|
| 10 | 1 | 50 |
| 11 | 1 | 75 |
| 12 | 2 | 20 |
| 13 | 99 | 15 |
Ada has two orders, Grace one, Linus none, and order 13 points to a customer that does not exist.
INNER JOIN: only matches
SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
| name | order_id | total |
|---|---|---|
| Ada | 10 | 50 |
| Ada | 11 | 75 |
| Grace | 12 | 20 |
Linus (no orders) and order 13 (no customer) are both dropped. JOIN alone means INNER JOIN.
LEFT JOIN: keep every left row
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
| name | order_id |
|---|---|
| Ada | 10 |
| Ada | 11 |
| Grace | 12 |
| Linus | NULL |
Linus appears with NULL for the order columns. A classic use is finding rows without a match:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- customers who never ordered
RIGHT JOIN: keep every right row
RIGHT JOIN is the mirror image; here it would keep order 13 with a NULL name. Most developers swap the table order and use LEFT JOIN instead for readability.
FULL OUTER JOIN: keep everything
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
Returns matched rows plus unmatched rows from both sides: Linus with a NULL order and order 13 with a NULL name. PostgreSQL, SQL Server and Oracle support it; MySQL does not, so emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.
CROSS JOIN: every combination
SELECT c.name, s.size
FROM customers c
CROSS JOIN (VALUES ('S'), ('M'), ('L')) AS s(size);
No ON clause: the result has rows(a) × rows(b) rows. Useful for generating combinations, dangerous by accident.
Self join
A table can join to itself, for example to pair employees with managers:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
Join conditions: ON versus WHERE
For an INNER JOIN the two are equivalent. For an outer join they are not:
-- keeps all customers, only attaches paid orders
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
-- turns the LEFT JOIN into an INNER JOIN (drops customers with no paid orders)
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
Put filters on the optional table in the ON clause.
Common pitfalls
- Duplicate rows. One-to-many joins repeat the "one" side. Summing a customer-level column after joining orders multiplies it. Aggregate before joining or use
COUNT(DISTINCT ...). - NULLs never equal each other.
NULL = NULLis not true, so rows withNULLkeys never match in a join. - Ambiguous columns. Qualify column names with table aliases (
c.id). - Missing indexes. Index the join columns (
orders.customer_id) or large joins become slow. - Accidental cross join. A missing
ONcondition returns millions of rows.
Readable queries
Long join queries are easier to review when formatted consistently; paste yours into the SQL Formatter. Browse more patterns in SQL Query Examples, and export results to a spreadsheet by converting JSON with JSON to CSV.
Summary
| Join | Returns |
|---|---|
| INNER | rows matching in both tables |
| LEFT | all left rows, matching right rows or NULL |
| RIGHT | all right rows, matching left rows or NULL |
| FULL OUTER | all rows from both, matched where possible |
| CROSS | every combination |
When APIs return this data as JSON, see What Is JSON; for designing the endpoints, read REST API Basics.
Related tools
SQL Formatter
Format and minify SQL for PostgreSQL, MySQL, SQLite and more.
SQL Query Examples
Copy-ready SQL examples for common tasks, with formatting.
JSON to CSV Converter
Convert arrays of JSON objects to CSV, flattening nested data.
FAQ
INNER JOIN vs LEFT JOIN?
INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns every left row and fills right columns with NULL when there is no match.
Why did my join duplicate rows?
One-to-many relationships multiply rows: each left row repeats once per matching right row.
Does MySQL support FULL OUTER JOIN?
Not directly; emulate it with LEFT JOIN UNION RIGHT JOIN.
Keep reading
- REST API Basics
Understand REST APIs: resources, HTTP methods, status codes, JSON payloads, idempotency, pagination, versioning and authentication basics.
- What Is JSON and How Does It Work?
Learn what JSON is, its syntax rules, data types, common mistakes and how to read and write it in JavaScript and Python.