What are Subqueries in SQL?

Expected Interview Answer

Step-by-Step Explanation

1. Step 1

   Identify the inner need

   Determine what intermediate value or set the outer query depends on, such as an average or a list of IDs.

2. Step 2

   Write the subquery in parentheses

   Compose the inner SELECT that produces that value or set, wrapped in parentheses.

3. Step 3

   Choose the placement

   Put the subquery in WHERE, FROM, SELECT, or HAVING depending on how the outer query needs its result.

4. Step 4

   Pick the right operator

   Use =, IN, EXISTS, ANY, or ALL to match a scalar, list, or existence test to the outer query.

5. Step 5

   Validate independently

   Run the subquery alone first to confirm it returns the expected shape and values before nesting it.

Code Example

-- Scalar subquery in WHERE: employees earning above the average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Multi-row subquery with IN: orders from customers in India
SELECT order_id, amount
FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE country = 'India'
);

-- Derived table (subquery in FROM): average salary per department
SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg
WHERE avg_salary > 50000;

Follow-up Questions

• What is the difference between a correlated and a non-correlated subquery?
• When would you use a subquery instead of a JOIN?
• What is a scalar subquery?
• Why must a derived table have an alias?
• How do IN, EXISTS, ANY, and ALL differ when used with subqueries?