SQL Joins

SQL joins are the most-tested SQL topic in placement rounds — expect "types of joins", "INNER vs LEFT JOIN", and query-writing tasks. Knowing exactly which rows and NULLs each join produces is the core skill.

The join types

Join	Returns
INNER	only rows matching in both tables
LEFT (OUTER)	all left rows + matching right (NULLs where no match)
RIGHT (OUTER)	all right rows + matching left (NULLs where no match)
FULL OUTER	all rows from both (NULLs where no match)
CROSS	Cartesian product (every left × every right)
SELF	a table joined to itself

Key behaviours

An INNER JOIN drops unmatched rows on both sides. A LEFT JOIN keeps every left row, filling right-side columns with NULL when there's no match — ideal for "find records with no matching child" (add WHERE right.id IS NULL). A CROSS JOIN of m×n rows produces m·n rows.

Exam Tricks & Tips

• 🎯 INNER JOIN = intersection (matches only); FULL OUTER = union (all rows, NULLs for gaps).
• 🎯 To find rows in A with no match in B, use a LEFT JOIN … WHERE B.key IS NULL (the anti-join pattern).
• 🎯 A CROSS JOIN returns m × n rows — a missing join condition accidentally produces this Cartesian blowup.
• 🎯 A SELF JOIN joins a table to itself (needs aliases) — used for hierarchies like employee→manager.
• 🎯 JOIN with no qualifier means INNER JOIN by default in SQL.
• 🎯 Filtering an outer join in the WHERE clause (vs the ON) can silently turn it into an inner join — put outer-table conditions in ON.
• ❌ Common mistake: expecting a LEFT JOIN to drop unmatched left rows — it keeps them with NULLs; only INNER JOIN drops them.

Expected pattern

"Types of joins / difference between INNER and LEFT", "write a query to find unmatched rows", "what does CROSS JOIN produce?", "what is a self join?", and predicting the row/NULL output of a given join.

Quick recap

INNER = matches only; LEFT/RIGHT = all of one side + NULLs; FULL OUTER = all rows both sides; CROSS = Cartesian (m×n); SELF = table to itself. Use LEFT JOIN + IS NULL for anti-joins. Unqualified JOIN = INNER. Keep outer-table filters in ON, not WHERE.

Cover the answer, recall, then check. 12 cards on SQL joins.

Q1. What does an INNER JOIN return?
A1. Only rows that have matching values in both tables (the intersection).

Q2. What does a LEFT JOIN return?
A2. All rows from the left table plus matching right rows; right columns are NULL where there's no match.

Q3. What does a FULL OUTER JOIN return?
A3. All rows from both tables, with NULLs filled in wherever a side has no match (the union).

Q4. What does a CROSS JOIN produce?
A4. The Cartesian product — every row of the first table paired with every row of the second (m × n rows).

Q5. How do you find rows in table A with no match in table B?
A5. LEFT JOIN A to B and filter WHERE B.key IS NULL (the anti-join pattern).

Q6. What is a self join?
A6. Joining a table to itself (using aliases), e.g. to relate employees to their managers in the same table.

Q7. What join type does an unqualified JOIN mean?
A7. INNER JOIN — it's the default when no join type is specified.

Q8. Which join can silently blow up row counts if the ON condition is missing?
A8. It becomes a CROSS JOIN (Cartesian product), producing m × n rows.

Q9. Does a LEFT JOIN drop unmatched left rows?
A9. No — it keeps all left rows, filling unmatched right columns with NULL. Only INNER JOIN drops unmatched rows.

Q10. Where should you put outer-table conditions to preserve an outer join?
A10. In the ON clause — putting them in WHERE can turn the outer join into an inner join.

Q11. INNER JOIN corresponds to which set operation conceptually?
A11. Intersection of the matching rows; FULL OUTER JOIN corresponds to union.

Q12. If table A has 4 rows and B has 3, how many rows does A CROSS JOIN B give?
A12. 12 rows (4 × 3) — the Cartesian product.

Joins are how relational databases reassemble the data that normalization split across tables. Choosing the right join type — and predicting exactly which rows survive — is one of the most practical and heavily tested SQL skills.

Core idea: a join combines rows from two tables based on a related column. INNER JOIN keeps only matching rows; LEFT/RIGHT OUTER JOIN keeps all rows from one side (filling non-matches with NULL); FULL OUTER JOIN keeps all rows from both; CROSS JOIN produces the Cartesian product.

How it works

Beginner — INNER JOIN

SELECT e.name, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.id;   -- only employees WITH a matching dept

The ON condition is the match rule. An INNER JOIN returns a row only when both sides match — unmatched rows on either side disappear.

Intermediate — outer joins and NULLs

• LEFT JOIN: all rows from the left table; where the right has no match, right columns are NULL. ("Keep every employee, even those with no department.")
• RIGHT JOIN: mirror image — all right rows.
• FULL OUTER JOIN: all rows from both; NULLs fill non-matches on either side.

SELECT e.name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.id;   -- unmatched employees appear with NULL dept

The classic use of LEFT JOIN + WHERE d.id IS NULL is to find rows with no match (employees in no department).

Advanced — CROSS JOIN, SELF JOIN and NULL pitfalls

• CROSS JOIN: every left row paired with every right row → m × n rows (no ON). Rarely intended; usually the result of forgetting a join condition.
• SELF JOIN: a table joined to itself (with aliases) — e.g. find each employee's manager from an Employee(id, manager_id) table.

SELECT e.name AS emp, m.name AS manager
FROM Employee e JOIN Employee m ON e.manager_id = m.id;

NULL gotcha: a join predicate e.dept_id = d.id never matches when dept_id is NULL (NULL = anything is unknown, not true), so those rows drop from an INNER JOIN.

Worked example — INNER vs LEFT row counts

Employee: (Ann, dept 1), (Bob, dept 2), (Cy, dept NULL). Department: (1, HR), (2, IT).

-- INNER JOIN e.dept_id = d.id
Ann | HR
Bob | IT                 -> 2 rows  (Cy dropped: NULL dept has no match)

-- LEFT JOIN e.dept_id = d.id
Ann | HR
Bob | IT
Cy  | NULL               -> 3 rows  (Cy kept, dept columns NULL)

The difference is Cy: an INNER JOIN discards the unmatched row, a LEFT JOIN preserves it with NULLs. Predicting these counts — especially remembering that NULL keys never match — is the graded skill.

Interview & exam relevance

Very common: "difference between INNER and OUTER joins", "how do you find rows with no match" (LEFT JOIN + IS NULL), "what does a CROSS JOIN produce", and reading query output. A frequent trap: a query that accidentally becomes a Cartesian product because the join condition was omitted or wrong.

Tricks & gotchas

• INNER JOIN drops unmatched rows on both sides; LEFT keeps the left, RIGHT keeps the right.
• A missing/incorrect ON silently yields a CROSS JOIN (row explosion) — always check row counts.
• NULLs never satisfy an equality join predicate — unmatched NULL-key rows vanish from inner joins.
• Mnemonic: "INNER = intersection; LEFT = all-left + matches; FULL = union."

Worked Example

Problem: Given Employees(id, name, dept_id) and Departments(dept_id, dname), with one employee (Zoe) having dept_id = NULL and one department (Legal) having no employees, write and compare the results of an INNER JOIN and a LEFT JOIN, and show how to list departments with no employees.

Employees: (1,Amy,10), (2,Ben,20), (3,Zoe,NULL)
Departments: (10,Sales), (20,Tech), (30,Legal)

Solution:
INNER JOIN returns only rows with a match on both sides:

SELECT e.name, d.dname
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id;

Result: (Amy, Sales), (Ben, Tech). Zoe is dropped (NULL matches nothing — NULL = anything is unknown, never true), and Legal is dropped (no employee). INNER JOIN keeps only the intersection.

LEFT (OUTER) JOIN keeps every left row, filling unmatched right columns with NULL:

SELECT e.name, d.dname
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Result: (Amy, Sales), (Ben, Tech), (Zoe, NULL). All three employees appear; Zoe has no department, so dname is NULL. Legal still does not appear because Departments is the right table here.

To find departments with no employees, make Departments the preserved side and keep rows with no match:

SELECT d.dname
FROM Departments d
LEFT JOIN Employees e ON e.dept_id = d.dept_id
WHERE e.id IS NULL;

The LEFT JOIN gives every department (Legal's employee columns are NULL); the WHERE e.id IS NULL filter keeps only unmatched departments → Legal. This "anti-join" pattern is the standard way to find rows lacking a related record.

Answer: INNER JOIN → (Amy,Sales),(Ben,Tech) only (Zoe and Legal excluded). LEFT JOIN from Employees → adds (Zoe, NULL). To list employee-less departments, LEFT JOIN from Departments and filter WHERE e.id IS NULL → Legal.