Autocoder

SQL Subqueries Interview Questions

Last updated 2026-11-19

Subqueries are queries nested inside another query, and interviewers use them to test whether you can break a problem into steps: find the average, then compare against it; find the departments, then filter employees. The harder questions are about correctness, mainly the NOT IN with NULL trap, correlated subqueries and when EXISTS beats IN.

Every example runs on the employees and departments tables shown below, and the results are the real output. Syntax is standard and works on MySQL and PostgreSQL unless noted. Pair this page with SQL joins (many subqueries can be rewritten as joins) and SQL CTEs (the readable alternative).

TypesScalar, row, table, correlated
Sample dataemployees and departments
LevelEasy to hard

Sample data

Gita has a NULL department and Legal has no employees, so NULL handling matters in several answers.

sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);

Kinds of subquery

SQL Subqueries Interview Questions: questions with worked answers

  1. 1

    Find employees who earn more than the company average salary.

    easy

    A scalar subquery computes the average once; the outer query compares each salary with it. The average of the seven salaries is 64285.71, so Asha, Bilal and Farid qualify.

    sql
    SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY emp_id;
    -- name  | salary
    -- ------+-------
    -- Asha  | 90000
    -- Bilal | 75000
    -- Farid | 75000
  2. 2

    Find employees who earn more than the average of their own department (correlated).

    medium

    The inner query refers to the outer row through the alias e, so the average is recomputed per department. The same result can be produced with a join to a grouped derived table or a window function, which usually scales better. In Engineering the average is 80000, so only Asha qualifies; in Sales nobody exceeds 60000.

    sql
    SELECT e.name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) ORDER BY e.emp_id;
    -- name | salary
    -- -----+-------
    -- Asha | 90000
  3. 3

    What is the difference between a correlated and a non-correlated subquery?

    medium

    A non-correlated subquery does not refer to the outer query and can be evaluated once, then reused. A correlated subquery references outer columns, so it is evaluated, at least conceptually, for each candidate outer row, which can be slow on large tables without a supporting index. Modern optimisers often decorrelate such subqueries into joins, so check the execution plan rather than assuming. EXISTS and the per-department average above are correlated.

  4. 4

    Explain IN versus EXISTS. When does each win?

    medium

    IN compares a value against the set returned by the subquery. EXISTS returns true as soon as one matching row is found and ignores the selected columns, so SELECT 1 is a convention. Modern optimisers (PostgreSQL, MySQL 8, SQL Server) often plan both as the same semi join, so performance is usually similar. Choose EXISTS when you need multiple correlation conditions or when the subquery column can be NULL, because EXISTS has no NULL pitfall. Departments that have employees:

    sql
    SELECT dept_name FROM departments WHERE dept_id IN (SELECT dept_id FROM employees) ORDER BY dept_id;
    -- dept_name
    -- -----------
    -- Sales
    -- Engineering
    -- HR
    sql
    SELECT dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) ORDER BY dept_id;
    -- dept_name
    -- -----------
    -- Sales
    -- Engineering
    -- HR
  5. 5

    Why does NOT IN return no rows when the subquery contains NULL?

    hard

    x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last term is unknown, and TRUE AND UNKNOWN is UNKNOWN, so the row is never selected. Because Gita has dept_id NULL, the NOT IN query returns nothing, although Legal has no employees. Use NOT EXISTS, or filter the NULLs out in the subquery.

    sql
    SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees);
    -- (0 rows)
    sql
    SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);
    -- dept_name
    -- ---------
    -- Legal
  6. 6

    What do ANY and ALL do? Write the employees earning more than everyone in Sales.

    medium

    > ALL means greater than every value returned, and > ANY (or SOME) means greater than at least one. > ALL (...) is the same as > MAX(...) when the set is non-empty, and an empty set makes ALL true. MySQL and PostgreSQL support both; SQLite does not, so the demo uses the equivalent MAX form. Sales salaries are 60000 and 60000, so employees above 60000 are returned.

    sql
    SELECT name, salary FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept_id = 10) ORDER BY emp_id;
    -- MySQL and PostgreSQL: WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 10)
    -- name  | salary
    -- ------+-------
    -- Asha  | 90000
    -- Bilal | 75000
    -- Farid | 75000
  7. 7

    What is a derived table and why does it need an alias?

    medium

    A subquery in the FROM clause that acts as a temporary table for the outer query. MySQL and PostgreSQL require an alias, because the outer query must name the columns it reads. Derived tables are the way to filter on an aggregate result or a window function result, and to join a pre-aggregated set.

    sql
    SELECT d.dept_name, t.total FROM departments d JOIN (SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id) t ON t.dept_id = d.dept_id ORDER BY t.total DESC;
    -- dept_name   | total
    -- ------------+-------
    -- Engineering | 240000
    -- Sales       | 120000
    -- HR          | 50000
  8. 8

    Use a subquery in the SELECT list: show each employee with the department headcount.

    medium

    A scalar correlated subquery in SELECT must return exactly one row and one column, otherwise the database raises an error (more than one row returned by a subquery). It runs per row and is easy to read, but a join with a grouped derived table or a window COUNT usually runs faster at scale.

    sql
    SELECT e.name, (SELECT COUNT(*) FROM employees x WHERE x.dept_id = e.dept_id) AS dept_headcount FROM employees e ORDER BY e.emp_id;
    -- Gita shows 0: dept_id = NULL never matches, even her own row
    -- name   | dept_headcount
    -- -------+---------------
    -- Asha   | 3
    -- Bilal  | 3
    -- Chitra | 2
    -- Dev    | 2
    -- Esha   | 1
    -- Farid  | 3
    -- Gita   | 0
  9. 9

    Find the department with the highest average salary using a subquery.

    medium

    Compute per-department averages in a derived table and compare with its maximum. Engineering averages 80000, ahead of Sales (60000) and HR (50000).

    sql
    SELECT dept_id, AVG(salary) AS avg_sal FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING AVG(salary) = (SELECT MAX(a) FROM (SELECT AVG(salary) AS a FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id) t);
    -- dept_id | avg_sal
    -- --------+--------
    -- 20      | 80000
  10. 10

    Why does UPDATE employees SET ... WHERE salary < (SELECT AVG(salary) FROM employees) work in PostgreSQL but fail in MySQL for some forms?

    hard

    MySQL raises error 1093 ("You can't specify target table for update in FROM clause") when the subquery reads from the same table being modified, in some forms. The workaround is to wrap the subquery in a derived table so it is materialised first: WHERE salary < (SELECT a FROM (SELECT AVG(salary) AS a FROM employees) t). PostgreSQL and SQL Server allow reading the target table in the subquery, because each statement sees a consistent snapshot of the data.

  11. 11

    Find employees whose manager is in a different department.

    medium

    A subquery can fetch the manager's department for each employee. A self join expresses it more directly, but this is a typical correlated scalar example. The NULL cases (no manager, or no department) produce unknown and drop out.

    sql
    SELECT e.name FROM employees e WHERE e.dept_id <> (SELECT m.dept_id FROM employees m WHERE m.emp_id = e.manager_id) ORDER BY e.emp_id;
    -- name
    -- ------
    -- Chitra
    -- Esha
  12. 12

    What is a lateral subquery and when do you need it?

    hard

    LATERAL (PostgreSQL, MySQL 8.0.14+, Oracle CROSS APPLY, SQL Server CROSS APPLY and OUTER APPLY) lets a subquery in FROM refer to columns of tables listed before it, so you can run a top-N per group query: for each department, the two highest-paid employees. It is the readable answer to "top N per group" when a window function with a filter feels clumsy.

    sql
    SELECT d.dept_name, t.name, t.salary
    FROM departments d
    CROSS JOIN LATERAL (
      SELECT name, salary FROM employees e
      WHERE e.dept_id = d.dept_id
      ORDER BY salary DESC LIMIT 2
    ) t;   -- PostgreSQL; use JOIN LATERAL ... ON TRUE in MySQL 8.0.14+
  13. 13

    Subquery or join: which should you use?

    medium

    Use the one that expresses the intent most clearly and check the plan. A join is natural when you need columns from both tables. A subquery (EXISTS, IN) is natural for "has at least one" and "has none" tests, where a join would create duplicates that need DISTINCT. Correlated scalar subqueries in SELECT are the easiest to write and the easiest to make slow. Optimisers rewrite many forms into the same plan, so do not assume a join is always faster.

Frequently asked questions

How many levels of subquery are allowed?

Databases allow deep nesting (the limits are very high), but more than two levels is usually a sign to use a CTE for readability.

Can a subquery return more than one column?

In FROM, yes. In a scalar position, no. A row constructor can compare several columns at once in PostgreSQL and MySQL: (a, b) IN (SELECT a, b ...).

Is EXISTS faster than IN?

Not inherently. Modern optimisers often produce the same plan. EXISTS is safer with NULLs, and the difference shows mostly with older engines.

What is the next topic after subqueries?

CTEs: SQL CTEs and recursive queries.

Related guides

More in Programming Language Interview Questions by Language

See all in this section