SQL - Subquery in SQL

What Is a Subquery in SQL?

A subquery (also called a nested query) is a SQL query inside another query. It allows you to use the result of one query as input for another.

Subqueries are used when you want to:

• Filter data based on results from another table

• Compare a value to a calculated or grouped result

• Use a dynamic list of values in a WHERE or IN clause

Types of Subqueries

1. Subquery in the WHERE Clause

Used to filter rows based on the result of another query.

SELECT name FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments WHERE location = 'New York'
);

• This returns all employees who work in New York departments.

2. Subquery in the SELECT Clause

Used to calculate something for each row.

SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

• This returns each customer's name and how many orders they have.

3. Subquery in the FROM Clause

Used to create a temporary result set that the outer query can use.

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

• This calculates the average salary per department, then filters departments with average salary > 50,000.

Correlated vs. Non-Correlated Subqueries

Correlated Example:

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

• Compares each employee's salary to the average salary in their department.

 Where Can Subqueries Be Used?

• SELECT

• FROM

• WHERE

• HAVING

• INSERT, UPDATE, or DELETE statements