SQL Subqueries

A subquery in SQL is a query that is nested inside another query.
It is also called an inner query or nested query.
The main query that contains the subquery is called the outer query.

Subqueries are used to perform operations where the result of one query is used as input for another.
They help make complex queries more readable and reduce the need for multiple separate queries.

Why Use Subqueries?

• To break complex problems into smaller parts.
• To filter data based on results from another query.
• To avoid creating temporary tables.
• To perform calculations before using them in the main query.

Syntax of a Subquery

SELECT column1, column2
FROM table_name
WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

Types of SQL Subqueries

1. Single-Row Subquery

• Returns only one row.
• Commonly used with operators like =, <, >, <=, >=.

Example:

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

Here, the subquery calculates the average salary, and the outer query fetches employees earning above that average.

2. Multiple-Row Subquery

• Returns multiple rows.
• Used with operators like IN, ANY, ALL.

Example:

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);


The subquery finds departments in location 100, and the main query lists employees working in those departments.

3. Correlated Subquery

• The subquery depends on values from the outer query.
• It is executed once for each row of the main query.

Example:

SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Here, the subquery calculates the average salary for each department, comparing it with each employee’s salary.

4. Nested Subqueries

• A subquery inside another subquery.

Example:

SELECT name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location_id = (
        SELECT location_id
        FROM locations
        WHERE city = 'New York'
    )
);

The innermost query finds the location ID of New York, the middle query finds the department in that location, and the outer query fetches employees from that department.

Subqueries in Different Clauses

1. In the WHERE Clause

Most common usage, to filter results.

SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

2. In the FROM Clause

Subquery acts as a temporary table.

SELECT department_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
JOIN departments ON dept_avg.department_id = departments.department_id;

3. In the SELECT Clause

To calculate values dynamically.

SELECT name, salary, (SELECT AVG(salary) FROM employees) AS overall_avg_salary
FROM employees;

Advantages of Subqueries

• Simplifies complex queries.
• Avoids use of temporary tables.
• Makes SQL more readable.
• Useful for filtering and aggregations.

Limitations of Subqueries

• Can be slower than JOIN in some cases.
• Nested queries may be harder to debug.
• Not all database systems allow subqueries in every clause.

When to Use Subqueries vs Joins

• Use subqueries for filtering or computed values.
• Use joins when combining related data from multiple tables for better performance.

Summary Table

Conclusion:
SQL subqueries are a powerful tool for writing efficient and readable database queries. By embedding one query inside another, you can filter, compute, and transform data without writing multiple separate queries.