Stage 4. JOIN: Combining Relational Data Correctly

JOIN connects rows from multiple tables using a relationship condition. In relational systems this is a core skill: data is split by entities (users, orders, payments), and without JOIN you cannot build complete business views.

Why JOIN is needed

Example task: show order list with customer name. Name is in users, order is in orders. Reading those tables separately does not produce one row with “order + owner”. JOIN solves that.

Base pattern:

SELECT ...
FROM left_table l
JOIN right_table r ON r.key = l.key;

Critical part: the ON condition. It defines which rows match.

JOIN types from simple to advanced

1) INNER JOIN

Returns only rows that match on both sides.

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

Users without orders are excluded.

2) LEFT JOIN

Returns all rows from left table plus matched rows from right table. If no match exists, right-side fields are NULL.

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

A user without orders remains as name + NULL.

3) RIGHT JOIN

Mirror of LEFT JOIN: all rows from right table plus matches from left.

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

In practice, teams often prefer LEFT JOIN style for readability.

4) FULL OUTER JOIN

Returns matches plus unmatched rows from both sides.

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

Useful for reconciliation and orphan-record checks.

Example with output (INNER JOIN)

SELECT o.id, o.amount, o.status, u.name
FROM orders o
INNER JOIN users u ON u.id = o.user_id
ORDER BY o.id;

Common mistakes and fixes

1. Missing ON condition: creates Cartesian explosion.
2. Wrong post-filter after LEFT JOIN: WHERE o.status = 'PAID' may remove left-side unmatched rows.
3. Ignoring cardinality: one user can have many orders, so row count growth is expected.

Safer pattern for right-side filter in LEFT JOIN:

LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'PAID'

How to learn JOIN without confusion

1. Start with two-table INNER JOIN.
2. Rewrite same case with LEFT JOIN and compare output.
3. Add third table (payments) and observe row-count growth.
4. Then move to FULL OUTER JOIN and reconciliation cases.

Practice Before the Next Lesson

Run four queries (INNER, LEFT, RIGHT, FULL) on users and orders. After each run, answer: “Which rows remained and why?” This is the fastest way to internalize JOIN behavior.