SQL Joins MCQ with Answers – Online Test

What this topic covers

31 subtopics

INNER JOIN3
LEFT JOIN3
Scalar subquery3
Self join3
Correlated subquery2
Derived table2
Join cardinality2
NOT EXISTS2
NOT IN with NULL2
ON vs WHERE2
ALL / ANY1
Ambiguous columns1

Show all 31 subtopics

Anti-join1
CROSS JOIN1
EXISTS1
FULL OUTER JOIN emulation1
IN subquery1
Join multiplicity1
Join performance1
JOIN syntax1
Join with aggregation1
LEFT JOIN with GROUP BY1
LEFT JOIN with SUM1
Multiple self joins1
Nested subquery1
Query rewriting1
Recursive CTE1
RIGHT JOIN1
Self join with aggregation1
Semi-join1
USING1

Beginner 15
Intermediate 15
Advanced 15

About this topic

Joins combine rows from two or more tables using a related column. Interviewers test them with result-prediction questions, because small details – a condition in ON instead of WHERE, a NULL in a subquery, COUNT(*) instead of COUNT(column) – change the answer completely. All questions below use the same two tables, employees and departments.

Free preview

Sample questions

Try each one first, then open the answer for the explanation and why the other options are wrong.

Question 1 INNER JOIN Beginner Practice

Which join returns only the rows that have a matching value in both tables?

LEFT JOIN

FULL OUTER JOIN

INNER JOIN

CROSS JOIN

Show answer and explanation

Correct answer C

Explanation

An INNER JOIN keeps a row only when the join condition is true for a row in each table. Rows without a partner are dropped from both sides.

Why the other options are wrong

ALEFT JOIN also keeps left-table rows that have no match (with NULLs).
BFULL OUTER JOIN keeps unmatched rows from both tables.
DCROSS JOIN pairs every row with every row; it has no matching condition at all.

Question 2 INNER JOIN Beginner Practice Query executed

What does this query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

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

name	dept
Asha	Engineering
Meena	Engineering
John	Sales
Zoya	Sales
NULL	HR

name	dept
Asha	Engineering
John	Sales

name	dept
Asha	Engineering
Ravi	NULL
Meena	Engineering
John	Sales
Zoya	Sales

name	dept
Asha	Engineering
Meena	Engineering
John	Sales
Zoya	Sales

Show answer and explanation

Correct answer D

Explanation

Ravi has dept_id = NULL, and NULL = 10 is never true, so he has no match. HR has no employees. An inner join drops both, leaving four rows in employee-id order.

Why the other options are wrong

AKeeping HR with NULL is what a RIGHT JOIN would do.
BA join returns every matching pair, not one row per department.
CKeeping Ravi with NULL is what a LEFT JOIN from employees would do.

Question 3 LEFT JOIN Beginner Practice Query executed

How many rows does this query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT COUNT(*) AS total
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

total
3

total
4

total
6

total
5

Show answer and explanation

Correct answer D

Explanation

A LEFT JOIN keeps every row of the left table (employees). Each employee matches at most one department, so the result has exactly 5 rows; Ravi's row simply has NULL department columns.

Why the other options are wrong

A3 is the number of departments.
B4 is the inner-join count; it drops Ravi.
C6 would also add HR, which only a RIGHT or FULL join keeps.

Question 4 INNER JOIN Beginner Practice Query executed

Which departments have at least one employee?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT DISTINCT d.name
FROM departments d
JOIN employees e ON e.dept_id = d.id
ORDER BY d.name;

name
Engineering
HR
Sales

name
Engineering

name
Engineering
Sales

name
Engineering
Engineering
Sales
Sales

Show answer and explanation

Correct answer C

Explanation

The join produces one row per employee (two per department). DISTINCT collapses the duplicates, and HR has no match so it never appears.

Why the other options are wrong

AHR has no employees, so the inner join drops it.
BSales has John and Zoya.
DThat is the result without DISTINCT.

Question 5 Anti-join Intermediate Practice Query executed

Which query correctly lists the departments that have no employees?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

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

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

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

SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

Show answer and explanation

Correct answer A

Explanation

The LEFT JOIN keeps HR with NULLs in the employee columns, and e.id IS NULL selects exactly those unmatched rows. This "anti-join" pattern is the safe way to find rows without a partner.

Why the other options are wrong

B= NULL is never true; NULL must be tested with IS NULL.
CAn inner join never produces unmatched rows, so the WHERE finds nothing.
DRavi's dept_id is NULL, so the subquery contains NULL. 30 NOT IN (10, NULL, 10, 20, 20) is UNKNOWN, not true – the query returns no rows. Use NOT EXISTS instead.

Question 6 LEFT JOIN with GROUP BY Intermediate Practice Query executed

What does this query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

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

name	staff
Engineering	3
HR	0
Sales	2

name	staff
Engineering	2
Sales	2

name	staff
Engineering	2
HR	0
Sales	2

name	staff
Engineering	2
HR	1
Sales	2

Show answer and explanation

Correct answer C

Explanation

The LEFT JOIN keeps HR with a NULL employee. COUNT(e.id) counts only non-NULL values, so HR gets 0. Ravi has no department, so he is not counted anywhere.

Why the other options are wrong

ARavi (dept_id NULL) does not match Engineering.
BDropping HR is what an inner join does.
DHR = 1 is what COUNT(*) gives: it counts the NULL-filled row.

Question 7 ON vs WHERE Intermediate Practice Query executed

The salary filter is written in the ON clause, not in WHERE. What is returned?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e
  ON e.dept_id = d.id AND e.salary > 60000
ORDER BY d.id, e.id;

dept	name
Engineering	Asha
Engineering	Meena
Sales	Zoya

dept	name
Engineering	Asha
Engineering	Meena
Sales	NULL
Sales	Zoya
HR	NULL

dept	name
Engineering	Asha
Engineering	Meena
Sales	Zoya
HR	NULL

dept	name
Engineering	Asha
Engineering	Meena
Sales	John
Sales	Zoya
HR	NULL

Show answer and explanation

Correct answer C

Explanation

In a LEFT JOIN, a condition in ON only decides which right-side rows match; it never removes left-side rows. So every department appears, HR with NULL, and John (50,000) simply does not match Sales.

Why the other options are wrong

ALosing HR is what happens when the filter is in WHERE: NULL > 60000 is not true, so the row is removed.
BAn unmatched right-side row is not shown as NULL alongside a department that has other matches.
DJohn earns 50,000 and fails the ON condition.

Question 8 NOT EXISTS Intermediate Practice Query executed

What does this query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

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

name
HR
Ravi

name
(no rows)

name
HR

name
Engineering
Sales

Show answer and explanation

Correct answer C

Explanation

For each department, the correlated subquery looks for an employee in it. Only HR has none, so only HR passes NOT EXISTS. Unlike NOT IN, NULLs in employees.dept_id do not affect the result.

Why the other options are wrong

AThe query returns department names only.
BEmpty is what NOT IN returns here, because of the NULL dept_id.
DThose departments do have employees.

Question 9 NOT IN with NULL Intermediate Practice Query executed

How many departments does this query count?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT COUNT(*) AS total FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

total
3

total
1

total
2

total
0

Show answer and explanation

Correct answer D

Explanation

The subquery returns 10, NULL, 10, 20, 20. For HR, 30 NOT IN (…, NULL, …) means 30 <> NULL, which is UNKNOWN, so the row is not counted. The others are in the list. Result: 0 – a classic interview trap.

Why the other options are wrong

A3 would mean nothing matched at all.
B1 is what NOT EXISTS (or NOT IN with "WHERE dept_id IS NOT NULL") would give.
CEngineering and Sales are both in the list.

Question 10 Nested subquery Advanced Practice Query executed

What does this classic "second highest salary" query return?

departments

id	name
10	Engineering
20	Sales
30	HR

employees

id	name	dept_id	salary	manager_id
1	Asha	10	90000	NULL
2	Ravi	NULL	40000	1
3	Meena	10	75000	1
4	John	20	50000	1
5	Zoya	20	65000	4

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

second_highest
65000

second_highest
90000

second_highest
NULL

second_highest
75000

Show answer and explanation

Correct answer D

Explanation

The inner query finds the top salary (90,000); the outer query takes the maximum of everything below it – 75,000. It also handles ties at the top correctly.

Why the other options are wrong

A65,000 is the third highest.
B90,000 is excluded by the WHERE.
CNULL would appear only if every employee earned the same.

35 more questions in the timed setsSame format as the samples, with a timer, a question palette and a full review at the end.

Start practising

Joins and Subqueries: questions with answers

What this topic covers

About this topic

Sample questions

All 10 topics