T TestsWorld
← All topic guides

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

  1. Anti-join pattern. To find "rows in A with no partner in B", use A LEFT JOIN B ... WHERE B.key IS NULL.
  2. 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.
  3. 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

  1. Filtering the right table in WHERE after a LEFT JOIN. It silently converts the query into an INNER JOIN.
  2. Using = NULL instead of IS NULL. WHERE e.id = NULL returns nothing.
  3. Counting with COUNT(*) after an outer join. Unmatched groups show 1 instead of 0.
  4. 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.
  5. Forgetting table aliases in self joins. FROM emp JOIN emp is ambiguous; write FROM emp e JOIN emp m ON e.manager_id = m.id.

Practice now: https://www.testsworld.com/mocks

Ready to practise SQL JOINs?
Take a free mock test

Related guides