SQL Joins Explained With Everyday Examples

Why joins exist

In a relational design, information is split across tables so that each fact is stored once. Customer details live in one table and orders in another. A join puts the pieces back together for a particular question, by matching rows on a shared value, usually a primary key in one table and a foreign key in the other.

Think of two lists on a desk: a list of club members and a list of the events each member signed up for. To produce a sheet showing each member’s name next to their events, you match entries by member number. That matching is a join.

The example tables

The examples use two small, made-up tables.

customers

orders

Ana has two orders, Ben has one, and Chloe has none. That last detail is what makes the different join types behave differently.

INNER JOIN: only the matches

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

The ON clause states how rows match. An inner join keeps only rows that have a partner on both sides. The result:

Chloe is missing because she has no orders. Ana appears twice because she matches two orders. A join produces one result row per matching pair, which is why a “one” side repeats when it has several “many” partners.

Plain JOIN usually means INNER JOIN.

LEFT JOIN: keep everything from the first table

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

A left join keeps every row from the table on the left (here, customers) and fills in matching data from the right. Where there is no match, the right-hand columns come back as NULL:

This is the right choice for questions like “list all customers, including those who have not ordered.” To find only the customers without orders, add a filter:

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

That returns Chloe.

RIGHT JOIN: the mirror image

A right join keeps every row from the right-hand table and fills in matches from the left. In this example it would list all orders and their customers. Because it is just a left join with the tables swapped, many people simply write left joins and reorder the tables, which keeps queries consistent and easier to read.

FULL JOIN: keep everything from both sides

A full join returns all rows from both tables, matched where possible and padded with NULL where not. With the example data, the result would look the same as the left join, because every order has a customer. A full join becomes interesting when both sides can have unmatched rows, such as comparing two lists to see what is on one but not the other. Not every database product supports it directly.

CROSS JOIN: every combination

A cross join pairs every row of one table with every row of another, with no matching condition. With 3 customers and 3 orders it would return 3 × 3 = 9 rows. It is occasionally useful for generating combinations, but it is usually a sign of a mistake when it happens by accident, because the row count grows quickly.

Joining more than two tables

Joins can be chained. If there were also an order_items table, you could join customers to orders, then orders to order_items, each with its own ON condition. Add one join at a time and check the results as you go.

Using aliases for shorter queries

Table aliases make queries easier to read:

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

Here c and o stand for the two tables. This also avoids ambiguity when both tables have a column with the same name.

Common mistakes

• Forgetting the ON condition. Some systems then treat the join as a cross join, producing far more rows than expected.
• Duplicated rows. If the second table has several matches, the first table’s data repeats. This matters when you then add up values: summing a customer-level number after joining to orders can count it more than once.
• Filtering a left join in WHERE. Putting a condition on the right-hand table in WHERE can quietly remove the NULL rows, turning your left join into an inner join. Putting that condition in the ON clause keeps the unmatched rows.
• Joining on the wrong columns. Matching on a column that is not truly a key, such as a name, can pair up unrelated rows.
• Ignoring NULLs. A NULL in a key column never matches anything, including another NULL.

Checking your result

A reliable habit is to count rows before and after a join. If you expect one row per customer and get more, something is multiplying. Starting with a small slice of data makes this easy to see. The basics of choosing columns and filtering are covered in SQL queries, and the logic of keys behind these joins is explained in relational databases.

When joins get slow

On large tables, joins can be slow if the matching columns are not indexed. Database indexes often make the difference, and the same matching work is why very large analytical queries are often run in data warehouses designed for them.