Real data almost never lives in one table. Customers sit in one table, their orders in another, and the moment you need both together, you need a join. This is a practical walk through what joins are, the main types, when to reach for each one, and how they actually behave with real rows.
What Are Joins?
A join combines rows from two or more tables based on a related column between them, usually a key that exists in both. Instead of running separate queries and stitching the results together in application code, a join lets the database do that matching for you in a single query.
To make the examples concrete, imagine two tables: customer, which holds one row per customer, and orders, which holds one row per order, linked back to a customer through customer_id.
customer
| customer_id | item_purchased | category |
|---|---|---|
| 1 | Running Shoes | Footwear |
| 2 | Backpack | Bags |
| 3 | Water Bottle | Accessories |
orders
| order_id | customer_id | order_total |
|---|---|---|
| 101 | 1 | 59.99 |
| 102 | 1 | 24.50 |
| 103 | 4 | 12.00 |
Notice customer 3 has no matching order, and order 103 belongs to customer 4, who doesn't exist in the customer table. That mismatch is exactly what different join types handle differently.
Types of Joins
| Join | Returns |
|---|---|
| INNER JOIN | Only rows with a match in both tables |
| LEFT JOIN | All rows from the left table, matched rows from the right, or NULL |
| RIGHT JOIN | All rows from the right table, matched rows from the left, or NULL |
| FULL OUTER JOIN | All rows from both tables, matched where possible, NULL where not |
| SELF JOIN | A table joined to itself, useful for hierarchical or comparative data |
| CROSS JOIN | Every row from one table paired with every row from the other |
When to Use Each
INNER JOIN is the default choice when you only care about records that exist on both sides, like customers who have actually placed an order.
LEFT JOIN is for when the left table is the source of truth and you want every one of its rows represented, even if there's nothing to match on the right, such as listing every customer whether or not they've ordered yet.
RIGHT JOIN is the mirror image of LEFT JOIN. It's less commonly used in practice, since most people just swap the table order and use LEFT JOIN instead, but it's useful when the query is already structured around the right table.
FULL OUTER JOIN is for reconciliation work: finding customers with no orders and orders with no matching customer, all in one result set.
SELF JOIN comes up with hierarchical data, like an employee table where each row has a manager_id pointing to another row in the same table.
CROSS JOIN is rare in everyday querying, but useful for generating combinations, like pairing every product with every size option.
Practical Examples
An inner join returns only customers who have placed at least one order.
SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
INNER JOIN orders o ON c.customer_id = o.customer_id;
A left join keeps every customer, even ones with no orders, filling in NULLs where there's nothing to match.
SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
A right join keeps every order, even ones whose customer_id doesn't exist in the customer table.
SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
A full outer join surfaces every mismatch in both directions at once, useful for spotting orphaned records.
SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
A self join compares rows in the same table, here finding customers who bought the same category as someone else.
SELECT a.item_purchased, b.item_purchased AS also_bought
FROM customer a
JOIN customer b ON a.category = b.category AND a.customer_id <> b.customer_id;
A cross join pairs every row with every other row, here generating every category and discount combination for a promo matrix.
SELECT c.category, d.discount_label
FROM customer c
CROSS JOIN discount_tiers d;
A join's ON clause decides the match. A LEFT, RIGHT, or FULL decides what happens when there isn't one. Get those two decisions right and the rest of the query usually falls into place.
The Simple Way to Remember It
INNER JOIN keeps only what matches on both sides. LEFT and RIGHT JOIN keep everything from one side no matter what. FULL OUTER JOIN keeps everything from both sides. SELF JOIN compares a table to itself. CROSS JOIN pairs everything with everything.
Start by asking which rows you can afford to lose. That answer almost always points to the right join.
Top comments (0)