Joins: Connecting the Dots

Combining Tables

Data in a relational database is normalized — deliberately split across multiple tables to eliminate redundancy. A Users table stores who someone is; an Orders table stores what they bought. To answer "What did Alice buy?", you need to combine both tables. That's what JOINs do.

Every join requires two things: the tables to combine, and the join condition — the column(s) that link them. Typically this is a foreign key relationship.

-- Setup context:
-- Users:  (id, name)
-- Orders: (id, user_id, amount)

SELECT users.name, orders.amount
FROM   users
JOIN   orders ON users.id = orders.user_id;

The Main Join Types

1. INNER JOIN
Returns only rows where a match exists in both tables. If a user has no orders, they are excluded entirely.

SELECT users.name, orders.amount
FROM   users
INNER JOIN orders ON users.id = orders.user_id;

Use case: "Show me all users who have placed at least one order."

2. LEFT (OUTER) JOIN
Returns all rows from the left table (users), plus matching rows from the right (orders). Where no match exists, right-side columns are NULL.

SELECT users.name, orders.amount
FROM   users
LEFT JOIN orders ON users.id = orders.user_id;

Use case: "Show me all users, and their order amount if they have one — otherwise NULL."

3. RIGHT (OUTER) JOIN
The mirror of LEFT JOIN. Returns all rows from the right table, with NULLs for unmatched left-side rows. In practice, most engineers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order — it's easier to reason about.

4. FULL OUTER JOIN
Returns all rows from both tables. Where no match exists on either side, the unmatched columns are NULL. Useful for finding rows that exist in one table but not the other.

SELECT users.name, orders.amount
FROM   users
FULL OUTER JOIN orders ON users.id = orders.user_id;

5. CROSS JOIN (Cartesian Product)
Returns every possible combination of rows from both tables. No join condition is needed — or wanted.

SELECT * FROM colors CROSS JOIN sizes;
-- 5 colors × 4 sizes = 20 rows

Warning: A CROSS JOIN on two tables with 10,000 rows each produces 100,000,000 rows. This is one of the most common causes of accidental database overload. If you see a query running for minutes and returning enormous result sets, check for an accidental CROSS JOIN (often caused by a missing ON clause in an older FROM a, b syntax).