JOINs combine rows from multiple tables based on a related column — essential for querying relational data spread across tables. The join type determines which rows are kept when there's no match.
users.name, orders.total
users
orders users.id orders.user_id;
The ON clause specifies how the tables relate (matching a foreign key to a primary key). The join type controls what happens for rows that don't match.
INNER JOIN → only rows that MATCH in BOTH tables (the intersection)
users WITH orders only — users with no orders are excluded
LEFT JOIN → ALL rows from the LEFT table + matches from the right (NULL if no match)
ALL users, with their orders (or NULL for users with no orders)
RIGHT JOIN → ALL rows from the RIGHT table + matches from the left (NULL if no match)
ALL orders, with their user (mirror of LEFT)
FULL JOIN → ALL rows from BOTH tables (matched where possible, NULL where not)
-- INNER — only users who have orders
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;
-- LEFT — ALL users, even those with no orders (o.total is NULL for them)
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN + IS NULL → users who have NEVER ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- no matching order → o.id is NULL
The LEFT JOIN + IS NULL pattern finds rows in one table with no corresponding row in another — a very common, useful technique.
INNER → A ∩ B (matches only)
LEFT → all A + matching B
RIGHT → all B + matching A
FULL → all A + all B
JOINs are fundamental to relational databases — data is deliberately split across related tables (users, orders, products), and JOINs are how you combine that related data in queries, so understanding them is essential for virtually any non-trivial database work.
Understanding the four join types and what they do with non-matching rows is the key skill: INNER JOIN (only matching rows — the most common), LEFT JOIN (all rows from the left table, with NULLs for non-matches — crucial when you need all records regardless of matches), RIGHT JOIN (the mirror), and FULL JOIN (all rows from both).
Choosing the right join type directly determines your results — using INNER when you meant LEFT silently drops rows (a common bug), so understanding the distinction is important for correctness.
The LEFT JOIN + IS NULL pattern (finding rows with no match in another table — like users who never ordered) is a particularly useful, common technique worth knowing.
Since relational data is split across tables and JOINs are the primary way to query it together, and since the join type critically affects which rows appear in results, mastering JOINs — the four types, their behavior with non-matches, and patterns like anti-joins — is core, frequently-applied knowledge for SQL and one of the most important and commonly-tested database concepts.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate