SQL Joins Explained with One Tiny Dataset (Plus 5 Interview Questions)

Most join tutorials show you a Venn diagram and move on. Then an interviewer asks, "Why did your row count go up after the join?" or "Why did your LEFT JOIN behave like an INNER JOIN?", and the diagram doesn't help.

In this post we'll use one small dataset (two tables, ten rows in total) and run every join type on it, so you can see exactly which rows survive. Then we'll work through five interview-style questions that test whether you really understand joins, not just the syntax.

Everything below runs on SQLite 3.39 or newer (and on PostgreSQL). If you're on MySQL, everything works except FULL OUTER JOIN, which MySQL doesn't support. We show the usual workaround below.

The setup

Copy this into any SQL editor:

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  dept_id    INTEGER REFERENCES departments(id),
  manager_id INTEGER REFERENCES employees(id),
  salary     INTEGER
);

INSERT INTO departments VALUES
  (1, 'Engineering', 'Bengaluru'),
  (2, 'Sales',       'Mumbai'),
  (3, 'HR',          'Pune'),
  (4, 'Legal',       'Delhi');

INSERT INTO employees VALUES
  (1, 'Asha',  1, NULL, 150000),
  (2, 'Ravi',  1, 1,     90000),
  (3, 'Priya', 2, 1,     80000),
  (4, 'Karan', 2, 3,     60000),
  (5, 'Neha',  NULL, 1,  70000),
  (6, 'Arjun', 3, 1,     65000);

Two details are there on purpose, because they're what make joins interesting:

• Neha has no department (dept_id is NULL).
• Legal has no employees.

Keep an eye on those two rows. The difference between join types comes down to what happens to them.

INNER JOIN: only matching rows

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

name	dept
Asha	Engineering
Ravi	Engineering
Priya	Sales
Karan	Sales
Arjun	HR

Neha is gone (she matches no department) and so is Legal (it matches no employee). An inner join keeps a row only when the ON condition is true for both sides. JOIN on its own means INNER JOIN.

LEFT JOIN: keep everything from the left table

SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

You get the same five rows plus Neha | NULL. Every employee shows up. Where there's no match, the right-hand columns are filled with NULL. Legal still doesn't appear, because departments is the right table here.

RIGHT JOIN: the mirror image

SELECT e.name, d.name AS dept
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Now Legal appears as NULL | Legal, and Neha disappears. In practice most people just swap the table order and write a LEFT JOIN, because it reads more naturally.

FULL OUTER JOIN: keep everything from both

SELECT e.name, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Seven rows: the five matches, Neha | NULL, and NULL | Legal. On MySQL you can get the same result with a LEFT JOIN ... UNION ... RIGHT JOIN of the same two tables.

CROSS JOIN: every combination

SELECT COUNT(*) AS combos
FROM employees CROSS JOIN departments;

That returns 24 (6 employees × 4 departments). You rarely want this on purpose. Usually it shows up by accident when someone forgets the ON condition. If your result ever has "rows × rows" in it, check your join condition first.

Note: SQL doesn't promise any row order unless you write ORDER BY. Your rows may come back in a different order from the tables above. That's fine.

5 interview questions on joins

These are the kinds of questions that separate "I've used joins" from "I understand joins." Try each one yourself before you read the answer.

1. Find departments that have no employees

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;

Result: Legal. This is the anti-join pattern: LEFT JOIN, then keep only the rows where the right side didn't match. Check a column that can never be NULL in a real match, such as the primary key e.id. NOT EXISTS is an equally good answer, and it's worth mentioning both.

2. List each employee with their manager's name

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This is a self join: the same table, under two aliases. Interviewers like to ask why it's a LEFT JOIN. The answer: Asha has no manager, and with an INNER JOIN she would disappear from the list.

3. Why did my LEFT JOIN turn into an INNER JOIN?

Compare these two queries:

-- Version A: filter in WHERE
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 70000;

-- Version B: filter in ON
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id AND e.salary > 70000;

Version A returns 3 rows (Engineering twice, Sales once). Version B returns 5: the same 3, plus HR | NULL and Legal | NULL.

Why? WHERE runs after the join. The unmatched rows have e.salary = NULL, and NULL > 70000 is not true, so WHERE throws them away. Your LEFT JOIN quietly becomes an inner join. If the question is "show every department, and any well-paid employees in it," the filter on the right table belongs in ON.

4. Count employees per department, including empty ones

SELECT d.name, COUNT(*) AS wrong, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name;

name	wrong	headcount
Engineering	2	2
Sales	2	2
HR	1	1
Legal	1	0

COUNT(*) counts rows, and the LEFT JOIN produced one row for Legal (full of NULLs). COUNT(e.id) counts only non-NULL values, which gives the right headcount of 0. Mixing these two up is a classic way to fail this question.

The same idea explains the "why did my row count go up?" question. In a one-to-many join, each department row is repeated once for every matching employee. That's correct behaviour. Just remember it before you SUM a column from the "one" side.

5. Highest-paid employee in each department

Version with a correlated subquery:

SELECT d.name AS dept, e.name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id
);

Version with a window function:

SELECT dept, name, salary
FROM (
  SELECT d.name AS dept, e.name, e.salary,
         RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
  FROM employees e
  JOIN departments d ON e.dept_id = d.id
)
WHERE rnk = 1;

Both return Asha (Engineering), Priya (Sales) and Arjun (HR). A good follow-up to bring up yourself is ties: RANK() keeps every employee tied for the top salary, while ROW_NUMBER() would pick exactly one of them.

A quick mental checklist

Before you run any join, ask yourself:

1. Which rows must not disappear? That table goes on the left of a LEFT JOIN.
2. Is this one-to-one or one-to-many? If it's one-to-many, expect repeated rows and be careful with aggregates.
3. Where does each filter go? Filters on the optional (right) table usually belong in ON.
4. What am I counting? COUNT(*) counts rows. COUNT(col) counts non-NULL values.

Try it in the browser

Reading about joins only gets you so far. You need to write a few that go wrong. Our hands-on SQL practice has chapters on INNER JOIN, outer joins and NULL handling, with exercises you run in the browser. Nothing to install.

A good exercise: extend the dataset above with a projects table (id, name, dept_id) and write a query that lists every department with its number of employees and its number of projects. If you get inflated counts, go back to question 4.

Happy querying!

— Schoolabe team