Subqueries in SQL

Subqueries in SQL

🔹 What is a Subquery?

A subquery is a query inside another SQL query.
It is often used in the WHERE, FROM, or SELECT clause to provide intermediate results.

🔹 Types of Subqueries

  1. Single-row subquery → returns only one value
  2. Multi-row subquery → returns multiple values
  3. Nested subquery in SELECT or FROM → used as derived tables or calculated fields

🔹 Examples

1. Subquery in WHERE

Find employees who earn more than the average salary:

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

👉 The inner query (SELECT AVG(salary) FROM employees) returns one value (average salary).
👉 The outer query then compares each employee’s salary to it.

2. Subquery in IN

Find employees who work in departments located in New York:

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

👉 The subquery returns multiple department IDs.
👉 The outer query checks if an employee’s department is in that list.

3. Subquery in FROM (Derived Table)

Find the highest salary per department:

SELECT dept_id, MAX(salary) AS highest_salary
FROM (
    SELECT department_id AS dept_id, salary
    FROM employees
) AS dept_salaries
GROUP BY dept_id;

👉 The inner query creates a temporary table (dept_salaries).
👉 The outer query aggregates from it.

4. Subquery in SELECT

Show employees and how much above the average salary they earn:

SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

👉 The subquery calculates the average salary.
👉 Each row shows the difference between an employee’s salary and that average.

✅ Key Notes:

  • Subqueries can return scalars (single value), lists, or even tables.
  • They can be correlated (depend on outer query) or non-correlated (independent).

Leave a Reply