LEFT, RIGHT, SELF & Multi-Table JOINs

Why we need more than INNER JOIN

In the last lesson, an INNER JOIN returned only rows that match in both tables. That is often exactly right — but not always. Imagine you want a list of every user, with their orders if they have any. A user who has never ordered would simply vanish from an INNER JOIN, because there is no matching order row. To keep those unmatched rows, we need an OUTER join.

Here are the join types you will actually use, in plain words:

LEFT JOIN — keep every row on the left

We will reuse the users and orders tables from the JOINs lesson, but add one detail: a third user, Meera (id 3), who has placed no orders. A LEFT JOIN starts from users and keeps all users, attaching order rows where they exist and filling the order columns with NULL (an empty “no value” marker) where they do not.

SELECT users.name, orders.id AS order_id, orders.total
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

Reading it: FROM users makes users the left table, so every user is guaranteed to appear. LEFT JOIN orders ON orders.user_id = users.id attaches each user’s orders. orders.id AS order_id renames that column in the output (AS gives a column a friendly alias) so it does not clash with users.id.

A very common follow-up is “find users who have never ordered”. You take the LEFT JOIN and keep only the rows where the order side came back empty:

SELECT users.name
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

WHERE orders.id IS NULL keeps only the rows the join could not match — exactly the users with no orders. Note we write IS NULL, not = NULL: in SQL you always test for an empty value with IS NULL / IS NOT NULL.

RIGHT JOIN

A RIGHT JOIN is the mirror image — it keeps all rows from the right table instead. In practice most developers just use LEFT JOIN and reorder the tables, because it reads more naturally (“give me all X, with their Y”). It is worth recognising RIGHT JOIN in other people’s code, but you rarely need to write one.

CROSS JOIN — every combination of two tables

A CROSS JOIN is different from the others: it has no ON condition and pairs every row of the first table with every row of the second. The result is called a Cartesian product — if one table has 3 rows and the other has 4, you get 3 × 4 = 12 rows. You reach for it when you genuinely want all combinations, such as building a grid of every size paired with every colour, or every day paired with every room for a timetable.

Imagine a tiny sizes table (S, M, L) and a colours table (Red, Blue). A CROSS JOIN lists every size-and-colour pair you could make:

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

Notice there is no ON clause — that is the signature of a CROSS JOIN. MySQL simply takes each row from sizes and attaches every row from colours in turn. With 3 sizes and 2 colours you get 3 × 2 = 6 rows: one for each possible pairing.

SELF JOIN — a table joined to itself

Sometimes the two related things live in the same table. Classic example: an employees table where each row has a manager_id that points to another row in the same table. To list each employee next to their manager’s name, you join employees to itself, giving the table two different aliases so MySQL can tell the two copies apart.

SELECT emp.name AS employee, boss.name AS manager
FROM employees AS emp
LEFT JOIN employees AS boss ON emp.manager_id = boss.id;

We pretend there are two tables: emp (the employee) and boss (their manager), but both are really the same employees table under different nicknames. emp.manager_id = boss.id matches an employee to the row of the person they report to. We use a LEFT JOIN so the top boss — who has no manager (manager_id is NULL) — still appears.

Joining three or more tables

Real schemas link many tables. To answer “which product was in which order, and who the customer was”, you chain joins: each new JOIN ... ON attaches one more table. Suppose order_items links orders to products:

SELECT users.name, products.title, order_items.quantity
FROM orders
INNER JOIN users        ON orders.user_id   = users.id
INNER JOIN order_items  ON order_items.order_id = orders.id
INNER JOIN products     ON order_items.product_id = products.id;

Read it top to bottom: start FROM orders, attach the users table to get the customer name, attach order_items to get the line items in each order, then attach products to get each item’s title. Every JOIN adds one ON condition saying how the new table links to one already in the query. There is no limit to how many tables you can chain this way.