Employees With No Direct Reports (Anti-Join)
HR wants to identify individual contributors -- employees nobody reports to. Using a correlated NOT EXISTS subquery...
SubqueriesCorrelated SubqueryNOT EXISTSAnti-Join
Intermediate
6m25 pts
1100%
SQL Practice Hub
SQL Subqueries Practice Questions
Nest one query inside another when you need its answer first.
16 questionsFreeRuns in your browser
- Scalar subquery
- Returns one value, usable in SELECT or a comparison.
- Correlated subquery
- Refers to the outer row and runs once per row.
- Subquery vs JOIN
- Pick whichever reads clearer and runs faster.
What you will practice
- Use a scalar subquery to compare each row against an overall value such as the company average
- Write correlated subqueries with EXISTS and NOT EXISTS to test for related rows
- Filter with IN versus EXISTS and understand the NULL trap in NOT IN
- Put a subquery in the FROM clause as a derived table to aggregate twice
- Recognise when to rewrite a nested subquery as a CTE or a join for readability
Read the full overview
A subquery is a SELECT nested inside another statement, used when one query needs the answer to a smaller query first. A scalar subquery returns a single value and can sit in the SELECT list or a comparison, while a correlated subquery references the outer row and is re-evaluated for each of them. These questions build the judgement for when a subquery is the clearest tool and when a join or CTE would read better and run faster.
16 questionsPage 2 of 2
FAQ
SQL Subqueries practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
Should I use a subquery or a JOIN?
Both can often give the same result. Choose by readability and by which columns you need.
- JOIN: when you need columns from both tables in the result.
- Subquery: when you only filter by, or compute one value from, the other table.
- EXISTS usually reads best for "has at least one" checks (shown here).
- Modern optimizers often produce the same plan, so check EXPLAIN before rewriting for speed.
SQL
-- customers with at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);What is a correlated subquery?
A correlated subquery references a column from the outer query, so it is logically re-evaluated for every outer row.
- Typical use: compare each row to a value for its own group, like each salary against its department average (shown here).
- It can be slow on large tables if the database cannot optimize it away.
- Often rewritable as a JOIN to a pre-aggregated subquery, or as a window function.
SQL
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);Why does NOT IN return no rows?
If the subquery returns even one NULL, NOT IN returns no rows at all.
- x NOT IN (1, NULL) evaluates to unknown, never true.
- Use NOT EXISTS instead (shown here); it is NULL-safe.
- Or add WHERE col IS NOT NULL inside the subquery.
SQL
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.