Subqueries

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

The inner query runs first and returns a single value (scalar subquery). The outer query uses it as a comparison value. This is the most common subquery pattern.

SELECT name
FROM employees
WHERE department_id = (
  SELECT department_id
  FROM employees
  WHERE name = 'Rahul Sharma'
);

Subquery in WHERE clause finds the department_id first, then outer query filters by it. Assumes only one employee named Rahul Sharma (else use IN instead of =).

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

This uses a subquery in the FROM clause (derived table). The inner query adds a row number per department, the outer query filters to top 3. This is the window function approach.

-- Find employees who earn more than their department average:
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

A correlated subquery references the outer query (e1.department_id). It re-runs for each row of the outer query — slower but powerful. The inner query here calculates avg per department dynamically.

-- IN: checks if value is in a list
SELECT * FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'Noida'
);

-- EXISTS: checks if subquery returns any rows
SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.id = e.department_id
  AND d.location = 'Noida'
);

EXISTS stops as soon as it finds one matching row (faster for large datasets). IN materialises the full subquery result first. For large subqueries, EXISTS is usually more efficient.