SQL Joins Explained with Examples

A join combines rows from two tables using a related column. Almost every real query touches more than one table, so joins are among the most useful things to understand well.

The examples use two tiny tables.

customers

orders

Carla has no orders, and order 13 belongs to nobody. Those two rows are what make the join types behave differently.

INNER JOIN: only the matches

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

Result: Asha (10), Asha (11), Ben (12). Carla disappears because she has no order, and order 13 disappears because it has no customer. JOIN on its own means INNER JOIN.

LEFT JOIN: keep everything from the left table

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

You get the three matches plus Carla, with NULL in the order columns. Use a left join whenever the question is "for every customer, show their orders if they have any".

A classic follow-up is finding rows with no match, called an anti-join:

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

This returns Carla only.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is a left join with the tables swapped: it keeps every order, including order 13 with a NULL customer name. Most people simply rewrite it as a LEFT JOIN by swapping the table order, which is easier to read.

A FULL OUTER JOIN keeps unmatched rows from both sides, so you would see Carla and order 13 in one result.

The WHERE-versus-ON trap

Suppose you want every customer and only their paid orders. This looks right but is not:

-- Wrong: the WHERE removes Carla again
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

For Carla, o.status is NULL, the comparison is not true, and her row is filtered out, so the query behaves like an inner join. Put the condition on the joined table in the ON clause instead:

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

Habits that save time

• Always write the ON condition. Forgetting it produces a cross join, where every row meets every row, and a 10,000 by 10,000 join returns 100 million rows.
• Qualify column names with table aliases (, ) so queries stay readable and never become ambiguous when a column is added later.
• Index the columns you join on, usually foreign keys such as .
• Check row counts after joining. If a result is larger than expected, a one-to-many relationship is multiplying rows.