Topic 26 of 52
Cross Table Joins
Overview
JOINs combine rows from two or more tables based on a related column (usually a foreign key). They are the core SQL mechanism for querying normalized data and are the most commonly tested SQL topic in interviews.
Syntax
sql
-- JOIN types and their behavior:
-- INNER JOIN: only rows with matching values in BOTH tables
-- LEFT JOIN: ALL rows from left + matching from right (NULLs for no match)
-- RIGHT JOIN: ALL rows from right + matching from left (NULLs for no match)
-- FULL OUTER JOIN: ALL rows from both (NULLs where no match)
-- CROSS JOIN: every row × every row (cartesian product)
-- Join syntax
SELECT columns
FROM table_a
[JOIN TYPE] JOIN table_b ON table_a.column = table_b.column;
-- Multiple tables
SELECT u.name, o.total, p.name AS product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;Common Pitfalls
- Always alias tables in multi-table queries — without aliases, column names become ambiguous and queries are unreadable.
- JOIN order matters for readability but not for results (the optimizer reorders them anyway). Start with the main table, then join related tables.
- Interview tip: Drawing Venn diagrams helps visualize JOIN types — INNER is the intersection; LEFT includes the full left circle; FULL OUTER is both circles combined.
Real-World Example
Choosing the right join type for different business questions:
example
sql
-- Q: Which products have been ordered? (INNER JOIN — only ordered products)
SELECT DISTINCT p.name FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id;
-- Q: Which products have NEVER been ordered? (LEFT JOIN + IS NULL)
SELECT p.name FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;
-- Q: All users and their order counts (LEFT JOIN — include users with 0 orders)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Q: All orders including those with no matching user (bad data check)
SELECT o.id, u.name
FROM orders o
FULL OUTER JOIN users u ON o.user_id = u.id
WHERE o.user_id IS NULL OR u.id IS NULL;