SQL Subquery: Types, Examples, and Interview Patterns

SELECT name, salary, department_id
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Interview note: This is the most common subquery question at phone-screen level. The interviewer checks whether you know to use a scalar subquery vs. a self-join. Both work, but the subquery is 3 lines shorter.

SELECT customer_id, name, email
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Interview note: If the interviewer asks you to rewrite this without IN, use EXISTS. Both produce identical execution plans in PostgreSQL 14+ and Snowflake. The choice is style, not performance.

SELECT d.department_name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date >= '2024-01-01'
);

Interview note: "SELECT 1" inside EXISTS is convention. The database ignores the SELECT list entirely. You could write SELECT * or SELECT 42. It does not affect correctness or performance.

SELECT dept_name, avg_salary
FROM (
  SELECT
    d.department_name AS dept_name,
    AVG(e.salary) AS avg_salary
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.department_name
) AS dept_averages
WHERE avg_salary > 85000;

Interview note: Most interviewers prefer the CTE version of this pattern. But some older codebases and Redshift workloads still use derived tables heavily. Know both.

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

Interview note: This is a top-5 SQL interview question. The faster alternative uses a CTE or window function (AVG() OVER (PARTITION BY department_id)), but interviewers sometimes specifically ask for the correlated version to test whether you understand row-by-row execution.

-- Find departments where the average salary exceeds 90,000.

-- Subquery (derived table)
SELECT dept_name, avg_sal
FROM (
  SELECT department_name AS dept_name, AVG(salary) AS avg_sal
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY department_name
) sub
WHERE avg_sal > 90000;

-- CTE
WITH dept_avg AS (
  SELECT department_name, AVG(salary) AS avg_sal
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY department_name
)
SELECT department_name, avg_sal
FROM dept_avg
WHERE avg_sal > 90000;

-- JOIN with HAVING
SELECT d.department_name, AVG(e.salary) AS avg_sal
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
HAVING AVG(e.salary) > 90000;

Interview note: All three produce identical results. The HAVING approach is shortest for this specific case. But the moment you need a second filter on the aggregated data (e.g., rank departments), you will need the CTE or subquery approach. Show the interviewer you know the tradeoffs.

-- For each order, find the most recent order from the same customer
SELECT o.order_id, o.customer_id, o.order_date, o.total
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

WITH ranked AS (
  SELECT
    order_id, customer_id, order_date, total,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, order_date, total
FROM ranked
WHERE rn = 1;

Interview note: If the interviewer asks for a correlated subquery, write it. Then say: For tables over a few hundred thousand rows, I would use ROW_NUMBER with a CTE instead. This demonstrates you know both patterns and understand the performance boundary.