SQL Joins

Why joins exist#

Data is split across tables on purpose (see the introduction page's note on the relational model) — a users table shouldn't repeat product and order details, and an orders table shouldn't repeat every user's name and email. A join recombines rows from two or more tables based on a related column, so a query can pull "who ordered what" back together at read time.

All the examples below use the users, products, and orders tables from the introduction page, with this sample data:

-- users
1  Amara Diallo   amara@example.com
2  Liam Chen      liam@example.com
3  Priya Nair     priya@example.com   -- has never placed an order

-- products
1  Wireless Mouse         24.99
2  Mechanical Keyboard    89.00
3  USB-C Hub              39.00       -- never ordered

-- orders
1  user_id=1  product_id=1  quantity=2
2  user_id=1  product_id=2  quantity=1
3  user_id=2  product_id=2  quantity=1
4  user_id=99 product_id=1  quantity=1  -- orphaned: user_id 99 doesn't exist

(That last row wouldn't actually be possible with the foreign key constraint from the introduction page — it's included here purely to illustrate what LEFT/RIGHT joins would do with unmatched rows if it existed.)

INNER JOIN#

INNER JOIN returns only rows where the join condition matches on both sides. A user with no orders won't appear at all; an order pointing at a nonexistent user wouldn't appear either.

SELECT u.name, o.id AS order_id, o.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Rows that come back:

name          order_id   quantity
Amara Diallo  1          2
Amara Diallo  2          1
Liam Chen     3          1

Priya Nair is missing (no matching order row) — that's the defining behavior of INNER JOIN.

LEFT JOIN#

LEFT JOIN (or LEFT OUTER JOIN) returns every row from the left table, plus matching rows from the right table where they exist. Where there's no match, the right table's columns come back as NULL.

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

Rows that come back:

name          order_id   quantity
Amara Diallo  1          2
Amara Diallo  2          1
Liam Chen     3          1
Priya Nair    NULL       NULL

This is the join to reach for whenever you need "all of A, whether or not it has a matching B" — a classic use case is finding rows with no match at all:

-- Users who have never placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN#

RIGHT JOIN is the mirror image of LEFT JOIN — every row from the right table, plus matches from the left where they exist:

SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

This returns every order, including the orphaned order_id = 4 (user_id = 99), with u.name coming back NULL for it. In practice, RIGHT JOIN is rarely used — almost anything written with RIGHT JOIN reads more naturally rewritten as a LEFT JOIN with the tables swapped, so most style guides prefer sticking to LEFT JOIN everywhere for consistency.

FULL (OUTER) JOIN#

FULL OUTER JOIN returns every row from both tables — matched where possible, NULL on whichever side has no match:

SELECT u.name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Rows that come back: every matched pair, plus Priya Nair / NULL (a user with no order) and NULL / order 4 (an order with no matching user).

MySQL has no native FULL OUTER JOIN — emulate it with a LEFT JOIN and a RIGHT JOIN combined with UNION:

SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.id AS order_id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

Comparison#

Self-joins#

A self-join joins a table to itself — useful whenever rows relate to other rows in the same table, such as an "employee reports to a manager" hierarchy stored in one table:

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INT
);

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

The table is aliased twice (e for the employee row, m for the manager row it points at) so the query can treat one physical table as two logical ones.

Joining three tables#

Joins chain naturally — this pulls the user's name, the product they ordered, and the quantity, all from three separate tables in one query:

SELECT u.name AS customer, p.name AS product, o.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
ORDER BY o.id;

Common mistakes#

• Forgetting the ON condition (or joining on the wrong columns), producing a cross join in disguise — every row of one table paired with every row of the other, multiplying the result set far beyond what was intended.
• Using INNER JOIN when the real requirement was "all of A even without a match" — silently dropping rows (like Priya Nair above) rather than surfacing them as NULL.
• Filtering a LEFT JOIN's right-table column in WHERE instead of in the ON clause, which silently turns it back into an INNER JOIN — e.g. WHERE o.status = 'shipped' discards unmatched left rows because NULL = 'shipped' is never true. Put that condition in the ON clause if unmatched rows should still be kept.
• Not aliasing tables in multi-join queries, leading to ambiguous column errors once two joined tables share a column name (both users and orders might have an id).