SQL Tutorial · Chapter 19 of 32
SQL joins combine rows from two or more tables based on a related column, usually a key such as CustomerID. Relational databases split data across tables to avoid repetition; a JOIN puts the pieces back together in one result. The four main types are INNER, LEFT, RIGHT and FULL JOIN, and they differ only in what happens to rows that have no match.
SQL JOIN Syntax
SELECT columns
FROM table1
[INNER | LEFT | RIGHT | FULL] JOIN table2
ON table1.key = table2.key;The ON clause states how the tables relate. Columns that exist in both tables must be prefixed with the table name or an alias so the database knows which one you mean.
Sample Tables
Customers and Orders are linked by CustomerID. Notice two deliberate gaps: customer 5 (Emma Brown) has no orders, and order 105 refers to customer 6, who is not in the Customers table.
| CustomerID | Name | City | Country |
|---|---|---|---|
| 1 | Anita Sharma | Mumbai | India |
| 2 | John Smith | London | UK |
| 3 | Maria Garcia | Madrid | Spain |
| 4 | Wei Chen | Singapore | Singapore |
| 5 | Emma Brown | Sydney | Australia |
| OrderID | CustomerID | Product | Amount | OrderDate |
|---|---|---|---|---|
| 101 | 1 | Laptop | 1200 | 2026-01-05 |
| 102 | 2 | Mouse | 25 | 2026-01-07 |
| 103 | 1 | Monitor | 300 | 2026-01-12 |
| 104 | 3 | Keyboard | 45 | 2026-02-02 |
| 105 | 6 | Headset | 80 | 2026-02-15 |
| 106 | 4 | Laptop | 1150 | 2026-03-01 |
The Four Join Types Compared
| Join type | Returns | Rows here |
|---|---|---|
| INNER JOIN | Only rows with a match in both tables | 5 |
| LEFT JOIN | All left rows, plus matches from the right (NULL if none) | 6 |
| RIGHT JOIN | All right rows, plus matches from the left (NULL if none) | 6 |
| FULL JOIN | All rows from both sides, NULL where no match | 7 |
Example 1: INNER JOIN
SELECT c.Name, o.OrderID, o.Product
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;| Name | OrderID | Product |
|---|---|---|
| Anita Sharma | 101 | Laptop |
| John Smith | 102 | Mouse |
| Anita Sharma | 103 | Monitor |
| Maria Garcia | 104 | Keyboard |
| Wei Chen | 106 | Laptop |
Emma Brown and order 105 both vanish because neither has a partner row.
Example 2: LEFT JOIN
SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerID;| Name | OrderID |
|---|---|
| Anita Sharma | 101 |
| Anita Sharma | 103 |
| John Smith | 102 |
| Maria Garcia | 104 |
| Wei Chen | 106 |
| Emma Brown | NULL |
Every customer appears; Emma Brown’s order columns are NULL. This is how you find customers who never ordered.
Example 3: RIGHT JOIN
SELECT c.Name, o.OrderID
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;Result: the five matched rows plus one extra row with Name NULL and OrderID 105, the order whose customer is missing. A RIGHT JOIN is simply a LEFT JOIN with the tables swapped.
Example 4: FULL JOIN
SELECT c.Name, o.OrderID
FROM Customers c
FULL JOIN Orders o ON o.CustomerID = c.CustomerID;Result: seven rows, including both Emma Brown with a NULL OrderID and order 105 with a NULL Name. FULL JOIN is ideal for reconciling two lists.
ON vs WHERE
ON describes how the tables match; WHERE filters the combined result. For INNER JOIN the two are interchangeable, but for outer joins they are not: a condition on the optional table placed in WHERE removes the NULL rows and silently turns your LEFT JOIN back into an INNER JOIN.
Works in MySQL, SQL Server, PostgreSQL, SQLite
- INNER, LEFT and RIGHT JOIN work in all four. MySQL and SQLite (before 3.39) lack FULL JOIN; emulate it with a LEFT JOIN UNION a RIGHT JOIN or, in older SQLite, two LEFT JOINs.
JOINalone means INNER JOIN everywhere;LEFT OUTER JOINandLEFT JOINare identical.- The old comma-join style
FROM Customers, Orders WHERE ...still runs but should be avoided.
Common Mistakes
- Forgetting ON, which produces a cross join with every combination of rows (30 rows here).
- Ambiguous column error:
SELECT CustomerIDwhen both tables have it. Prefix it. - Putting a right-table filter in WHERE after a LEFT JOIN and losing the unmatched rows.
- Joining on columns of different types, which forces conversions and skips indexes.
Practice Exercise
- List every customer’s Name and Country together with the Amount of each order, using an INNER JOIN.
- Which join would you use to list all orders, including those whose customer is missing from Customers?
Show answers
-- 1
SELECT c.Name, c.Country, o.Amount
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID;
-- 5 rows
-- 2: a RIGHT JOIN from Customers to Orders, or a LEFT JOIN with Orders on the left:
SELECT o.OrderID, c.Name
FROM Orders o
LEFT JOIN Customers c ON c.CustomerID = o.CustomerID;Related Chapters
- SQL INNER JOIN – matched rows only, in depth
- SQL LEFT JOIN – keep every row from the first table
- SQL FULL JOIN – keep everything from both sides
- SQL from Scratch: full course overview
FAQ
What is a JOIN in SQL?
A JOIN combines rows from two tables into one result by matching values in a related column, such as CustomerID in both Customers and Orders.
Which join is used most often?
INNER JOIN, because most reports only need rows that exist in both tables. LEFT JOIN is second, used whenever the second table’s data is optional.
Can I join more than two tables?
Yes. Add another JOIN … ON clause for each extra table. The database combines them one at a time in the order that is most efficient.
Chapter 19 of 32 · SQL from Scratch: all 32 chapters
