SQL Joins Explained for Interviews (with Examples You Can Run)

Joins come up in almost every SQL interview, from service-company online tests to product-company rounds. The good news: once you see joins working on a tiny dataset, they stop being confusing.

This post uses two small tables and shows the actual result of every query. All queries were run in SQLite 3.50, and they work the same way in PostgreSQL and MySQL (except FULL OUTER JOIN, which MySQL doesn't support; see below).

The example tables

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept_id INTEGER,     -- NULL means not assigned to a department yet
  manager_id INTEGER   -- NULL means no manager
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Finance');
INSERT INTO employees VALUES
  (1, 'Asha',  1,    NULL),
  (2, 'Ravi',  1,    1),
  (3, 'Meera', 2,    1),
  (4, 'Karan', NULL, 3),
  (5, 'Neha',  2,    3);

Notice the two "edge" rows that make joins interesting:

• Karan has no department (dept_id is NULL).
• Finance has no employees.

Keep an eye on what happens to these two in each join.

INNER JOIN: only matching rows

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

name  | department
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Neha  | Sales

An inner join keeps a row only when the ON condition finds a match on both sides. Karan has no department, and Finance has no employees, so both disappear. Writing just JOIN means INNER JOIN.

LEFT JOIN: every row from the left table

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

name  | department
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Karan | NULL
Neha  | Sales

A left join keeps every employee. When there's no matching department, the department columns are filled with NULL. Finance still doesn't appear, because it's on the right side and nobody matches it.

RIGHT JOIN: every row from the right table

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

name  | department
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Neha  | Sales
NULL  | Finance

This is the mirror image: every department is kept, and Finance appears with a NULL name. In practice, most people rewrite a right join as a left join with the tables swapped, because it reads more naturally. Both give the same rows.

FULL OUTER JOIN: everything from both sides

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id
ORDER BY e.id IS NULL, e.id;

name  | department
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Karan | NULL
Neha  | Sales
NULL  | Finance

A full join keeps unmatched rows from both tables: Karan and Finance are both here. MySQL has no FULL OUTER JOIN; the usual workaround there is a LEFT JOIN combined with a RIGHT JOIN using UNION.

Anti join: rows with no match

A very common interview question: "Find the 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;

name
Finance

The pattern is: left join, then keep only the rows where the right side is NULL. You can also write it with NOT EXISTS, which many interviewers like because it states the intent directly:

SELECT d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

name
Finance

Self join: a table joined to itself

"Show each employee with their manager's name." The manager is also an employee, so we join employees to itself with two different aliases:

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

employee | manager
Asha     | NULL
Ravi     | Asha
Meera    | Asha
Karan    | Meera
Neha     | Meera

We used a left join so that Asha, who has no manager, is still listed. With an inner join she would vanish. That detail is exactly what interviewers check.

Three join mistakes interviewers test

1. A WHERE condition that turns a LEFT JOIN into an INNER JOIN

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name <> 'Sales'
ORDER BY e.id;

name | department
Asha | Engineering
Ravi | Engineering

Karan is gone, even though this is a left join. His department is NULL, and NULL <> 'Sales' is not true (it's unknown), so WHERE removes the row. If you want to keep employees without a department, write WHERE d.name IS NULL OR d.name <> 'Sales'.

2. Filtering in ON vs in WHERE

For outer joins, a condition in ON decides which rows match, while a condition in WHERE decides which rows survive. Compare this with the query above:

SELECT d.name AS department, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id AND e.name LIKE 'R%'
ORDER BY d.id;

department  | name
Engineering | Ravi
Sales       | NULL
Finance     | NULL

Every department is still listed, because the name filter is part of the match, not a filter on the result.

3. COUNT(*) vs COUNT(column) after a LEFT JOIN

"Count the employees in each department, including departments with none."

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

name        | employees
Engineering | 2
Sales       | 2
Finance     | 0

Use COUNT(e.id), not COUNT(*). COUNT(*) counts rows, and the left join produces one row for Finance (with NULL employee columns), so it would wrongly report 1. COUNT(e.id) skips NULL values and correctly gives 0.

Quick summary

A CROSS JOIN has no condition and pairs every row with every row: 5 employees × 3 departments gives 15 rows. It's rarely what you want, and an accidental one (a missing join condition) is a common bug.

Practise more

Joins usually appear together with GROUP BY, subqueries and window functions in interview questions such as "second highest salary" or "employees earning more than their manager". You can read answered examples for free on our SQL interview questions page.