SQL Joins Explained with Simple Examples

A relational database keeps data in separate tables on purpose. Customers go in one table and their orders go in another, so a customer's name is stored once, not copied onto every order. That is tidy, but it creates a new question: how do you see a customer and their orders together? The answer is a join.

A join combines rows from two tables by matching values in a column they share. This entry explains the four joins you will meet most often, with two tiny tables you can follow by eye. It assumes you already know what a table, a row and a primary key are, and that you have seen a basic SELECT query.

The two tables

We will use the same data throughout. The customers table has one row per customer:

The orders table has one row per order. The column customer_id points to the customer who placed it. This kind of column is called a foreign key. Order 104 was a guest checkout, so its customer_id is empty (NULL).

Notice three facts: Ana has two orders, Ben has one, Chloe has none, and one order belongs to nobody. These facts are what make the four joins give different results.

The idea behind every join

All joins follow the same pattern:

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

Read it in plain words: "take each customer, find the orders whose customer_id equals the customer's id, and put them side by side". The part after ON is the matching rule. The word before JOIN decides what happens to rows that find no match.

The four joins you will meet most

INNER JOIN: only the matches

An inner join keeps only rows that have a match in both tables. Writing JOIN alone means the same as INNER JOIN.

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

Result:

Chloe is missing because she has no orders, and order 104 is missing because it has no customer. Use an inner join when you only care about things that are connected.

LEFT JOIN: keep everything from the first table

A left join keeps every row from the left table (the one named first, after FROM), and fills the right-side columns with NULL when there is no match.

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

Result:

Now Chloe appears, with empty order columns. Left joins answer questions such as "which customers have never ordered?". Add WHERE orders.id IS NULL and only Chloe remains.

RIGHT JOIN: keep everything from the second table

A right join is the mirror image. It keeps every row from the right table and fills the left side with NULL when nothing matches.

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

Result:

Now the guest order appears and Chloe does not. In practice, many people avoid right joins and swap the table order to use a left join instead, because it reads more naturally from left to right. The result is the same.

FULL JOIN: keep everything from both

A full join keeps all rows from both tables. Where there is no match, the missing side is NULL.

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

Result:

Full joins are the least common. Not every database system supports them directly. MySQL, for example, does not offer FULL JOIN, so you check the documentation of the system you use.

A quick way to choose

Joins with totals

Joins often work together with grouping. To count orders per customer, including customers with none:

SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name;

Ana gets 2, Ben gets 1 and Chloe gets 0. Chloe shows 0 because COUNT(orders.id) counts only non-empty values, and her id is NULL. If you had used an inner join, Chloe would not appear at all, and the report would quietly hide the customers with no orders.

Common mistakes

• Forgetting the matching rule. A join with no matching rule (a CROSS JOIN, or in some systems a JOIN written without ON) pairs every row of one table with every row of the other. Three customers and four orders would give twelve rows. On big tables this can be huge and slow.
• Using the wrong columns. Joining customers.id to orders.id runs without error but gives meaningless results. Always join a key to the column that refers to it.
• Being surprised by repeated rows. Ana appears twice because she has two orders. That is correct. If you add up a customer-level number after the join, you may count it twice.
• Picking INNER when you need LEFT. This silently drops rows. If a report looks too short, check the join type first.
• Ambiguous column names. Both tables have a column named id. Write customers.id or give tables short aliases so the database knows which one you mean.
• Filtering a LEFT JOIN in the wrong place. A condition in WHERE on the right-hand table can remove the NULL rows and turn your left join into an inner join. Put such conditions in the ON part if you want to keep unmatched rows.

FAQ

What is the difference between a join and a foreign key?

A foreign key is a rule stored in the table design that says a column must refer to a row in another table. A join is something you do when you read data. Joins work without foreign keys, but foreign keys help keep the data correct.

Is JOIN the same as INNER JOIN?

Yes. In standard SQL, JOIN by itself means INNER JOIN.

Does the order of tables matter?

For inner and full joins, the rows returned are the same either way, although column order may differ. For left and right joins, the order decides which table is kept in full.

Can I join more than two tables?

Yes. You add another JOIN line for each new table, each with its own matching rule.

Where do I go next?

Look at the other entries in the Data and Databases area, and try the examples on a free database tool installed on your computer. Typing the queries yourself teaches more than reading them.