SQL JOINs: formulas, shortcuts & solved examples
What it is
A JOIN combines rows from two or more tables using a related column, usually a foreign key. Almost every reporting query in an office, from a sales MIS to an HR headcount sheet, needs joins. Interview questions mostly ask you to predict which rows come out and how many.
Key rules
Join types
- INNER JOIN: only rows with a match on both sides.
- LEFT JOIN: every row of the left table; right-side columns are NULL when no match exists.
- RIGHT JOIN: mirror of LEFT. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping tables.
- FULL OUTER JOIN: all rows from both sides, NULLs where unmatched. MySQL has no FULL JOIN; emulate it with LEFT JOIN UNION RIGHT JOIN.
- CROSS JOIN: every combination, no condition.
- SELF JOIN: a table joined to itself with aliases, for example employee and manager in the same table.
Row-count formulas
For tables L (m rows) and R (n rows):
- CROSS JOIN = m × n rows.
- INNER JOIN ≤ m × n; with a one-to-many relation it equals the number of matched child rows.
- LEFT JOIN ≥ m (each left row appears at least once).
- FULL JOIN ≥ max(m, n).
NULL rule
NULL = NULL is not true in SQL, so a row with a NULL join key never matches anything in an INNER JOIN.
Shortcuts
- Anti-join pattern. To find "rows in A with no partner in B", use
A LEFT JOIN B ... WHERE B.key IS NULL. - WHERE turns LEFT into INNER. A filter on a right-table column in WHERE (such as
WHERE d.dname = 'HR') discards the NULL rows. Put such filters in the ON clause if you want to keep unmatched left rows. - COUNT(column), not COUNT(*). After a LEFT JOIN,
COUNT(*)counts the NULL-padded row as 1;COUNT(e.id)correctly gives 0.
Sample data
All examples use these two tables.
CREATE TABLE dept (id INT, dname TEXT);
INSERT INTO dept VALUES (10,'Sales'), (20,'HR'), (30,'Finance');
CREATE TABLE emp (id INT, name TEXT, dept_id INT);
INSERT INTO emp VALUES (1,'Asha',10), (2,'Ravi',20), (3,'Meena',NULL), (4,'Kiran',10);
Solved examples
Example 1: INNER versus LEFT
SELECT e.name, d.dname
FROM emp e JOIN dept d ON e.dept_id = d.id;
Result (3 rows):
| name | dname |
|---|---|
| Asha | Sales |
| Ravi | HR |
| Kiran | Sales |
Meena has a NULL dept_id, so she disappears. Change JOIN to LEFT JOIN and you get 4 rows, with Meena paired with NULL.
Example 2: Headcount per department
SELECT d.dname, COUNT(e.id) AS headcount
FROM dept d LEFT JOIN emp e ON e.dept_id = d.id
GROUP BY d.dname;
Result: Sales 2, HR 1, Finance 0.
Finance has no employees but still appears because dept is the left table. If you wrote COUNT(*) instead, Finance would wrongly show 1, since the NULL-padded row is still a row.
Example 3: Departments with no employees
SELECT d.dname
FROM dept d LEFT JOIN emp e ON e.dept_id = d.id
WHERE e.id IS NULL;
Result: Finance.
This anti-join is the standard answer to "find customers who never ordered" or "find branches with no staff". NOT EXISTS gives the same result and is often equally fast.
Example 4: CROSS JOIN size
SELECT COUNT(*) FROM emp CROSS JOIN dept;
Result: 12, which is 4 employees × 3 departments. Cross joins are useful for generating combinations, such as every store against every month for a sales grid, but an accidental cross join (a missing ON condition) is a classic cause of huge, wrong totals.
Common mistakes
- Filtering the right table in WHERE after a LEFT JOIN. It silently converts the query into an INNER JOIN.
- Using
= NULLinstead ofIS NULL.WHERE e.id = NULLreturns nothing. - Counting with COUNT(*) after an outer join. Unmatched groups show 1 instead of 0.
- Joining on a non-unique column and double counting. If both sides have duplicates on the join key, rows multiply; check with a quick
COUNT(*)before summing amounts. - Forgetting table aliases in self joins.
FROM emp JOIN empis ambiguous; writeFROM emp e JOIN emp m ON e.manager_id = m.id.
Practice now: https://www.testsworld.com/mocks