Context

Subqueries are a common SQL technique for breaking a problem into smaller steps, especially when you need to compare rows against aggregated or filtered results. Interviewers often use them to test whether you can structure query logic clearly.

Question

Explain how to solve SQL problems involving subqueries. In your answer, cover:

1. What a subquery is and where it can appear in a SQL statement
2. Common use cases such as filtering, aggregation, and row comparison
3. The difference between scalar, correlated, and table subqueries
4. When a subquery is a good choice versus when a join or CTE may be clearer

Scope Guidance

Keep the discussion practical. The interviewer is looking for a clear explanation of how subqueries work, the main patterns you would use in real SQL problems, and the most common mistakes to avoid.

Scalar Subquery

A scalar subquery returns a single value and is often used in SELECT, WHERE, or HAVING clauses. It is useful when comparing rows to an overall aggregate such as the average salary or maximum order amount.

SELECT employee_id, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Table Subquery

A table subquery returns multiple rows or columns and is commonly used with IN, EXISTS, or in the FROM clause as a derived table. It helps isolate intermediate result sets before applying additional logic.

SELECT customer_id
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) >= 3
);

Correlated Subquery

A correlated subquery references columns from the outer query and is evaluated in relation to each outer row. It is useful for row-by-row comparisons, though it can be harder to read and may be slower than equivalent joins in some cases.

SELECT e1.employee_id, e1.department_id, e1.salary
FROM employees e1
WHERE salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

EXISTS vs IN

Both EXISTS and IN can be used with subqueries, but they express slightly different intent. EXISTS is often preferred when checking whether at least one related row exists, while IN is convenient when comparing against a list of values.

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Derived Tables for Aggregation

A subquery in the FROM clause can compute an intermediate aggregation that the outer query then filters or formats. This is useful when aggregate results need further processing.

SELECT department_id, avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) dept_avg
WHERE avg_salary > 80000;