Lesson 4: Join on a relationship
Assume customers(customer_id, name) and orders(order_id, customer_id, total). Qualify columns with aliases whenever joined tables share column names.
SELECT c.customer_id, c.name, o.order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id;Join reference
8 patterns shown
| Join | Rows kept | Example |
|---|---|---|
| INNER JOIN | Only rows matching the ON condition in both tables. | JOIN orders o ON o.customer_id=c.customer_id |
| LEFT JOIN | Every left row; unmatched right columns become NULL. | LEFT JOIN orders o ON o.customer_id=c.customer_id |
| RIGHT JOIN | Every right row; unmatched left columns become NULL. | RIGHT JOIN customers c ON c.customer_id=o.customer_id |
| FULL OUTER JOIN | All rows from both sides, matching where possible. | FULL JOIN new_data n ON n.id=old_data.id |
| CROSS JOIN | Cartesian product: every left/right pair. | sizes CROSS JOIN colors |
| Self join | Join a table to itself using different aliases. | employees e JOIN employees m ON e.manager_id=m.id |
| USING | Join on same-named key columns and show one key column. | JOIN customers USING (customer_id) |
| Anti-join | Find unmatched left rows with LEFT JOIN and IS NULL. | LEFT JOIN orders o ON ... WHERE o.order_id IS NULL |
Protect the row count
A join can multiply rows when a key matches multiple records. Check uniqueness on both sides before assuming one-to-one output. Put right-side filters in the ON clause when unmatched left rows must remain; placing them in WHERE can turn a LEFT JOIN into an effective INNER JOIN.
Progress stays in this browser.