SQL JOINs
SQL JOINs
Visual Reference
INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN
A ∩ B A (+ B if match) B (+ A if match) A ∪ B
[A|AB|B] [A|AB| ] [ |AB|B] [A|AB|B]
INNER JOIN — Intersection
Returns only rows that match in both tables.
-- Users who have placed orders
SELECT u.name, o.order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
LEFT JOIN — All Left, Matched Right
Returns all rows from left table, NULLs where no match in right.
-- All users, including those with no orders
SELECT u.name, o.order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;
RIGHT JOIN — All Right, Matched Left
Returns all rows from right table. Less common — can always rewrite as LEFT JOIN.
-- Same result as above, tables swapped
SELECT u.name, o.order_id
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;
FULL OUTER JOIN — Union of Both
Returns all rows from both tables. NULLs where no match on either side.
-- All users and all orders, matched where possible
SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Rows with no match on either side
SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.order_id IS NULL;
CROSS JOIN — Cartesian Product
Every row from left × every row from right. No ON clause.
-- All combinations of colors and sizes
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
-- 4 colors × 3 sizes = 12 rows
-- Implicit cross join (older syntax, avoid)
SELECT * FROM a, b WHERE a.id = b.a_id; -- WHERE acts as INNER JOIN condition
SELF JOIN — Table Joins Itself
Join a table to itself. Always use aliases.
-- Employee + their manager name (both in employees table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Find employees earning more than their manager
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
Multiple JOINs
-- Order details: user + order + product
SELECT u.name, o.created_at, p.name AS product, oi.quantity
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered';
JOIN on Multiple Conditions
-- Match on two columns
SELECT *
FROM table_a a
JOIN table_b b ON a.user_id = b.user_id AND a.month = b.month;
-- JOIN with BETWEEN
SELECT *
FROM events e
JOIN date_ranges dr ON e.event_date BETWEEN dr.start_date AND dr.end_date;
Non-Equi JOIN
-- Salary bracket matching
SELECT e.name, e.salary, b.bracket_name
FROM employees e
JOIN salary_brackets b ON e.salary BETWEEN b.min_sal AND b.max_sal;
Anti-Join Pattern (NOT IN vs NOT EXISTS vs LEFT JOIN IS NULL)
-- Users who never ordered — 3 equivalent approaches
-- Method 1: LEFT JOIN + IS NULL (usually fastest)
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- Method 2: NOT EXISTS (good with correlated subquery)
SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- Method 3: NOT IN (careful — if subquery returns NULL, whole result is empty!)
SELECT u.name FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
Interview Questions
Q: INNER vs LEFT JOIN? → INNER returns only matched rows. LEFT returns all left-table rows; NULLs for unmatched right.
Q: When would FULL OUTER JOIN be useful? → Reconciliation queries — find rows in A not in B AND rows in B not in A in one pass.
Q: Why does NOT IN fail with NULLs?
→ x NOT IN (1, 2, NULL) → x != 1 AND x != 2 AND x != NULL → last condition is NULL (unknown) → entire expression NULL → row excluded. Use NOT EXISTS or filter NULLs.
Related
- [[Basics]] — SELECT, WHERE, aliases
- [[Subqueries & CTEs]] — correlated subqueries, EXISTS
- [[SQL for Interviews]] — classic join problems