SQL Subqueries & CTEs Interview Questions

4 questions with answers · SQL Interview Guide

Nested queries, correlated subqueries, Common Table Expressions, and the WITH clause. Used for breaking complex problems into readable steps.

bar_chartQuick stats

Total questions4

High frequency3

With code examples4

What is a subquery, and when would you use one

A subquery is a query nested inside another query, and it can appear in the SELECT, FROM, or WHERE clause depending on what you need. You'd use one when you need to filter based on a derived value, reference an aggregated result, or break a complex problem into steps. They're useful but can hurt performance if they run row-by-row (correlated subqueries), so it's worth considering whether a JOIN or CTE would be cleaner or faster.

sql

-- Find employees earning above the average salary
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

What is subquery

A subquery is a SELECT statement nested inside another SQL statement. It can be used in the WHERE clause to filter based on computed values, in the FROM clause to treat a derived result set as a temporary table, or in the SELECT clause to compute a value per row. Correlated subqueries reference the outer query and execute once per row, which can be slow on large datasets, so it's often worth replacing them with JOINs or CTEs when performance is a concern.

sql

-- Subquery in FROM clause (derived table)
SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_averages
WHERE avg_salary > 70000;

Explain correlated query work

A correlated subquery references a column from the outer query, which means it gets re-executed once for every row the outer query processes. This is different from a regular subquery, which runs once and returns a fixed result set. Because of that row-by-row execution, correlated subqueries can be slow on large tables, but they're useful when you need to compare each row against an aggregate or condition from a related set of rows.

sql

-- Find employees who earn more than the average salary in their own department
SELECT e1.employee_id, e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id  -- references outer query
);

What are the types of subquery

Subqueries fall into a few main categories. A scalar subquery returns a single value and can be used in a SELECT list or WHERE clause. A row subquery returns a single row with multiple columns. A table subquery returns multiple rows and columns and is typically used in FROM or JOIN clauses, where it acts like a derived table. You also have correlated subqueries, which reference columns from the outer query and are re-evaluated for each row, versus non-correlated subqueries that run once independently. EXISTS and IN subqueries are also common patterns worth mentioning.

sql

-- Scalar subquery
SELECT name, (SELECT MAX(salary) FROM employees) AS max_sal FROM employees;

-- Correlated subquery
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- Derived table (table subquery)
SELECT dept, avg_sal FROM (SELECT department_id AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS dept_avg;

arrow_backAll SQL interview questions

Knowing the answers is half the battle

The other half is explaining them clearly under pressure.

Try a free mock interviewarrow_forward

More SQL topics