Menu
CoddyTech
Start learning
Learn SQL step by stepOnline SQL compiler

SQL Subqueries and CTEs Interview Questions

Correlated subqueries, IN vs EXISTS, the NOT IN null trap, CTEs, recursion and UNION.

By Kevin Spektor, Co-founder & CTO

10 questions

  1. What is a subquery, and what is a correlated subquery?

    A subquery is a SELECT inside another statement. A correlated subquery uses a column of the outer query, so it is logically run once per outer row.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
-- employees who earn more than the average of their own department
SELECT name, dept, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept)
ORDER BY salary DESC;

    e.dept makes it correlated: Asha beats the Engineering average and Priya the Sales one. On a large table, AVG(salary) OVER (PARTITION BY dept) computes each average once. More in subqueries.

  2. What is the difference between IN and EXISTS?

    IN checks whether a value is in a list or a subquery's result; EXISTS checks whether a subquery returns any row and can stop at the first match.

    SQL
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 1, 500), (2, 1, 1500), (3, 2, 800);
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000
);

    Modern optimizers usually turn both into the same semi-join. The difference that matters is NOT IN versus NOT EXISTS: one NULL in the subquery makes NOT IN match nothing.

  3. Does NOT IN work when the subquery returns a NULL?

    SQL
    CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (1, 1), (2, NULL);
SELECT COUNT(*) FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

    It prints 0. id NOT IN (1, NULL) means id <> 1 AND id <> NULL; the NULL comparison is unknown, so no row passes. Use NOT EXISTS, which NULLs do not affect:

    SQL
    CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (1, 1), (2, NULL);
SELECT COUNT(*) FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

    That returns 2, Ravi and Meera.

  4. What is a CTE, and how is it different from a subquery or a temporary table?

    A CTE (common table expression) is a named subquery written before the main query with WITH name AS (...), alive for that one statement.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept
)
SELECT e.name, e.dept, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_salary
ORDER BY e.salary DESC;

    Unlike a subquery it can be referenced several times and can be recursive; unlike a temporary table it cannot be indexed or reused by later queries. MySQL has CTEs from 8.0. See common table expressions.

  5. How do you write a recursive CTE? Show an org chart with levels.

    An anchor query returns the starting rows, and a recursive query joins the CTE back to the table, combined with UNION ALL and repeated until no new rows appear.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH RECURSIVE chain(id, name, level) AS (
  SELECT id, name, 1 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT name, level FROM chain ORDER BY level, name;

    Asha is level 1, her four reports level 2, and Priya level 3. On cyclic data the recursion never ends by itself (SQL Server stops at 100 levels, MySQL at 1000), so add a depth limit like WHERE c.level < 20. More in recursive CTEs.

  6. When does a recursive CTE with WHERE x < 20 stop?

    SQL
    WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x * 2 FROM n WHERE x < 20
)
SELECT MAX(x) FROM n;

    WHERE x < 20 tests the row a step starts from, not the row it produces. From 16 the test passes and 32 is generated; from 32 it fails, so the max is 32. To cap the output itself, test the new value: WHERE x * 2 <= 20.

  7. Does a CTE make a query faster? Is it materialized?

    Not by itself. A CTE is for readability, and whether it is computed once (materialized) or inlined like a subquery depends on the database. PostgreSQL 12+ inlines a plain SELECT CTE referenced once and materializes one referenced more (override with AS MATERIALIZED or AS NOT MATERIALIZED); PostgreSQL 11 and older always materialized, which blocked filter pushdown. SQL Server always inlines, so a CTE used twice runs twice. See the PostgreSQL WITH docs, and check the plan before deciding.

  8. What is the difference between UNION and UNION ALL?

    Both stack the results of two queries; UNION removes duplicate rows and UNION ALL keeps every row, which is faster.

    SQL
    CREATE TABLE online_orders (customer TEXT);
INSERT INTO online_orders VALUES ('Asha'), ('Ravi');
CREATE TABLE store_orders (customer TEXT);
INSERT INTO store_orders VALUES ('Ravi'), ('Meera');
SELECT
  (SELECT COUNT(*) FROM (SELECT customer FROM online_orders
                         UNION SELECT customer FROM store_orders) AS u) AS union_rows,
  (SELECT COUNT(*) FROM (SELECT customer FROM online_orders
                         UNION ALL SELECT customer FROM store_orders) AS ua) AS union_all_rows;

    UNION gives 3 rows (Ravi once), UNION ALL gives 4. Both need the same number of columns with compatible types. Default to UNION ALL.

  9. What happens when you mix UNION and UNION ALL in one query?

    SQL
    SELECT COUNT(*) FROM (SELECT 1 UNION SELECT 1 UNION ALL SELECT 1) AS t;

    UNION and UNION ALL have equal precedence and run left to right: SELECT 1 UNION SELECT 1 gives one row, then UNION ALL SELECT 1 appends another, so 2. When you mix them, add parentheses so the intent is clear.

  10. Where can a subquery appear in a query: SELECT, FROM or WHERE?

    In all three. In SELECT it must be a scalar subquery (one value), in FROM it is a derived table with an alias (MySQL and SQL Server require one), and in WHERE or HAVING it feeds a comparison, IN or EXISTS.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT d.dept, d.people,
       (SELECT MAX(salary) FROM employees) AS company_max
FROM (SELECT dept, COUNT(*) AS people FROM employees GROUP BY dept) AS d
WHERE d.dept IN (SELECT dept FROM employees WHERE salary > 80000)
ORDER BY d.dept;

    It returns Engineering and Sales, each beside the company maximum of 120000; HR has nobody above 80000. A scalar subquery that returns two rows is an error in PostgreSQL, MySQL and SQL Server, while SQLite quietly takes the first row.

Coddy programming languages illustration

Learn to code with Coddy

Get started