SQL JOINs Explained with Examples: INNER, LEFT, RIGHT and FULL JOIN

Real data almost never sits in one table. Customers are in one table, orders in another, products in a third. To answer even a simple question like "how much did each customer buy?", you need to combine them. In SQL you do that with a JOIN.

This guide explains every JOIN type with small example tables and the exact result you get, so you can see what each one does instead of memorising diagrams.

Quick answer: INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table and fills missing matches with NULL. RIGHT JOIN does the same for the right table. FULL OUTER JOIN keeps all rows from both tables.

The example tables

customers

customer_id	name	city
1	Asha	Delhi
2	Ravi	Mumbai
3	Meena	Pune

orders

order_id	customer_id	amount
101	1	500
102	1	300
103	2	700
104	4	200

Notice two things: Meena (3) has no orders, and order 104 belongs to customer 4, who is not in the customers table. These two rows show the difference between the JOIN types.

JOIN syntax

SELECT columns
FROM table_a AS a
JOIN table_b AS b
    ON a.key = b.key;

The ON clause says which columns must match. Short aliases like c and o keep queries readable.

1. INNER JOIN: only matching rows

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

name	order_id	amount
Asha	101	500
Asha	102	300
Ravi	103	700

Meena is missing (no orders) and order 104 is missing (no customer). Writing just JOIN means INNER JOIN.

2. LEFT JOIN: all rows from the left table

SELECT c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id;

name	order_id	amount
Asha	101	500
Asha	102	300
Ravi	103	700
Meena	NULL	NULL

Every customer appears. Meena has NULL because she has no orders. LEFT JOIN is the one analysts use most, because you usually want to keep your main list complete.

Find rows with no match

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

Result: Meena. This pattern answers questions like "which customers never ordered?" or "which products never sold?".

3. RIGHT JOIN: all rows from the right table

SELECT c.name, o.order_id, o.amount
FROM customers AS c
RIGHT JOIN orders AS o
    ON c.customer_id = o.customer_id;

name	order_id	amount
Asha	101	500
Asha	102	300
Ravi	103	700
NULL	104	200

Every order appears, including 104 whose customer is missing. In practice most people swap the table order and use a LEFT JOIN instead, because it reads more naturally.

4. FULL OUTER JOIN: everything from both tables

SELECT c.name, o.order_id, o.amount
FROM customers AS c
FULL OUTER JOIN orders AS o
    ON c.customer_id = o.customer_id;

The result has all five rows: the three matches, Meena with NULL order, and order 104 with NULL name. Use it to compare two lists and find what is missing on either side.

MySQL does not support FULL OUTER JOIN. Combine a LEFT and a RIGHT JOIN with UNION instead:

SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

5. CROSS JOIN: every combination

SELECT s.size, c.colour
FROM sizes AS s
CROSS JOIN colours AS c;

3 sizes and 4 colours give 12 rows. There is no ON clause. Useful for building a grid, such as every product for every month, but be careful with big tables.

6. SELF JOIN: a table joined to itself

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

The same table is used twice with different aliases. This is how you show each employee next to their manager.

Joining more than two tables

SELECT c.name, p.product_name, oi.quantity
FROM orders AS o
JOIN customers AS c   ON o.customer_id = c.customer_id
JOIN order_items AS oi ON o.order_id = oi.order_id
JOIN products AS p    ON oi.product_id = p.product_id;

Add one JOIN per table. Each ON clause links the new table to one already in the query.

Common mistakes

1. Filtering a LEFT JOIN in WHERE

LEFT JOIN orders o ON ... WHERE o.amount > 400 removes Meena, because her amount is NULL. The LEFT JOIN quietly becomes an INNER JOIN. Put the condition in the ON clause instead if you want to keep all customers: ON c.customer_id = o.customer_id AND o.amount > 400.

2. Totals are too high after a join

When one customer has many orders, customer columns repeat once per order. If you then SUM a customer-level value (like credit limit), it is counted several times. Aggregate the many side first in a subquery, then join.

3. Joining on the wrong column

Always join on keys (IDs), not on names. Names can repeat or be spelled differently.

4. Forgetting the ON clause

Some databases treat a missing condition as a CROSS JOIN and return millions of rows. Always check your row count.

Which JOIN should I use?

Question	JOIN
Only records that exist in both tables	INNER JOIN
Keep my full main list, add details where available	LEFT JOIN
Records in A that are missing in B	LEFT JOIN + WHERE b.key IS NULL
Compare two lists both ways	FULL OUTER JOIN
Every combination	CROSS JOIN

Frequently asked questions

What is the difference between JOIN and INNER JOIN?

None. JOIN on its own means INNER JOIN.

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

None. The word OUTER is optional.

Is a JOIN slow?

Not if the join columns are indexed. Joins on primary and foreign keys are what databases are built for.

How is a SQL JOIN different from a relationship in Power BI?

A JOIN combines tables into one result when the query runs. A Power BI relationship keeps the tables separate and filters between them. See Power BI joins vs relationships.

Practice task

Using the two tables above, write a query that shows every customer with their total order amount, showing 0 for customers with no orders. Hint: LEFT JOIN, GROUP BY c.name and COALESCE(SUM(o.amount), 0).