Databases & SQL

SQL Joins Explained

Joins are how you combine data from multiple tables. The different types control which rows are included when there's no match on the other side.

Published April 20, 2026

A SQL JOIN combines rows from two or more tables based on a related column. The type of join controls what happens when a row in one table has no matching row in the other.

For these examples, assume two tables:

-- Table: customers
id | name
---|--------
 1 | Alice
 2 | Bob
 3 | Carol

-- Table: orders
id | customer_id | amount
---|-------------|-------
 1 |           1 |  50.00
 2 |           1 | 120.00
 3 |           2 |  75.00
-- Carol (id 3) has no orders

INNER JOIN

Returns only rows where there is a match in both tables. Rows without a match on either side are excluded.

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

-- Result:
-- Alice  |  50.00
-- Alice  | 120.00
-- Bob    |  75.00
-- Carol is excluded because she has no orders

INNER JOIN is the default. Writing just JOIN without a qualifier is the same as INNER JOIN.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. When there's no match in the right table, NULL fills the right-side columns.

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

-- Result:
-- Alice  |  50.00
-- Alice  | 120.00
-- Bob    |  75.00
-- Carol  |  NULL   <-- included, no matching order

Use LEFT JOIN when you want all rows from the primary (left) table regardless of whether a match exists. Classic use case: list all users and show NULL for those who haven't placed any orders.

To find rows in the left table with NO match in the right table, filter on the right side being NULL:

-- Find customers who have never placed an order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Result:
-- Carol

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN: returns all rows from the right table, plus matching rows from the left table. Less commonly used because you can always rewrite it as a LEFT JOIN by swapping the table order. Most teams prefer LEFT JOIN for consistency.

-- These two queries return the same result:
SELECT * FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;
SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

FULL OUTER JOIN

Returns all rows from both tables. Where there's no match, the missing side is filled with NULL. This is less common in application code but useful for finding discrepancies between two datasets.

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

-- Returns everything from both sides
-- Rows with no match on either side show NULLs

Note: MySQL does not support FULL OUTER JOIN natively. You can simulate it with a LEFT JOIN UNION ALL RIGHT JOIN where LEFT IS NULL.

CROSS JOIN

Returns the Cartesian product: every row from the left table combined with every row from the right table. No ON condition. Three customers and three orders gives nine rows. Rarely useful in application queries but handy for generating combinations (e.g., all possible seat/session pairings for a booking system).

SELECT c.name, t.size
FROM customers c
CROSS JOIN t_shirt_sizes t;

Joining more than two tables

You can chain multiple joins. Each one joins to the result of what came before:

SELECT c.name, o.amount, p.name AS product
FROM customers c
INNER JOIN orders o       ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p     ON oi.product_id = p.id;

Performance and indexes

Joins on unindexed columns are slow. The column in the ON condition should have an index on at least one side — usually the foreign key column. Running EXPLAIN on a slow query shows whether your join is using indexes:

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

Look for "Using index" in the Extra column. If you see "Full table scan" or "Using filesort" in an unexpected place, you may need to add an index or restructure the query.

The mental model that helps most: before writing the JOIN type, ask yourself "what should happen when there's no match?" If the row should be excluded, use INNER JOIN. If the row from the primary table should always appear, use LEFT JOIN. Everything else follows from there.