SQL

Subqueries in SQL

🔍 A Subquery (also called a nested query or inner query) is a query written inside another SQL query. The inner query executes first, and its result is used by the outer query. Subqueries make SQL more powerful by allowing one query to depend on the results of another query.

📖 What is a Subquery?

A subquery is a SELECT statement enclosed within parentheses. It can appear in different parts of an SQL statement, such as WHERE, HAVING, FROM, or SELECT.

Information

SQL executes the subquery first, then uses its result while executing the outer query.

🎯 Why Use Subqueries?

Subqueries help solve complex problems by breaking them into smaller, manageable queries.

📌 Retrieve data based on another query's result.
📌 Simplify complex SQL statements.
📌 Perform comparisons using calculated values.
📌 Filter records dynamically.
📌 Build advanced reports and analytics.

📊 Sample Table

Consider the following Employees table:

EmployeeID	EmployeeName	Department	Salary
101	Alice	IT	75000
102	Bob	HR	55000
103	Charlie	Finance	68000
104	David	IT	82000
105	Emma	HR	60000

📝 Basic Syntax

Subquery Syntax

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM table_name
    WHERE condition
);

💡 Simple Subquery Example

Retrieve employees whose salary is greater than the average salary.

Employees Above Average Salary

SELECT EmployeeName,
       Salary
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

SQL first calculates the average salary, then returns employees earning more than that value.

📍 Subquery in WHERE Clause

The WHERE clause is the most common place to use a subquery.

Employees in the IT Department

SELECT EmployeeName
FROM Employees
WHERE Department =
(
    SELECT Department
    FROM Employees
    WHERE EmployeeName = 'Alice'
);

📊 Subquery with IN

Use a subquery with IN when the inner query returns multiple values.

Employees in Selected Departments

SELECT EmployeeName,
       Department
FROM Employees
WHERE Department IN
(
    SELECT Department
    FROM Employees
    WHERE Salary > 70000
);

🔍 Subquery with EXISTS

EXISTS checks whether the subquery returns at least one row.

EXISTS with Subquery

SELECT EmployeeName
FROM Employees e
WHERE EXISTS
(
    SELECT 1
    FROM Employees
    WHERE Department = e.Department
      AND Salary > 80000
);

📈 Subquery in SELECT Clause

A subquery can calculate values for each row in the result.

Display Company Average Salary

SELECT EmployeeName,
       Salary,
       (
           SELECT AVG(Salary)
           FROM Employees
       ) AS AverageSalary
FROM Employees;

🗂️ Subquery in FROM Clause

A subquery inside the FROM clause acts like a temporary table.

Subquery in FROM

SELECT Department,
       AverageSalary
FROM
(
    SELECT Department,
           AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY Department
) AS DepartmentSummary;

📊 Correlated Subquery

A correlated subquery depends on values from the outer query. It executes once for each row processed by the outer query.

Employees Above Department Average

SELECT EmployeeName,
       Department,
       Salary
FROM Employees e
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE Department = e.Department
);

Important

Correlated subqueries are generally slower than non-correlated subqueries because they execute repeatedly for each row in the outer query.

⚖️ Types of Subqueries

Type	Description
Single-Row Subquery	Returns exactly one value.
Multiple-Row Subquery	Returns multiple rows.
Multiple-Column Subquery	Returns more than one column.
Correlated Subquery	References columns from the outer query.

💼 Real-World Example

A company wants to identify employees whose salary is greater than the average salary of their own department.

Department Salary Analysis

SELECT EmployeeName,
       Department,
       Salary
FROM Employees e
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE Department = e.Department
)
ORDER BY Department,
         Salary DESC;

This query compares each employee's salary with the average salary of their respective department.

⚠️ Common Mistakes

❌ Forgetting parentheses around the subquery.
❌ Using = when the subquery returns multiple rows instead of IN or ANY.
❌ Returning multiple columns where only one is expected.
❌ Writing inefficient correlated subqueries for large datasets.

Warning

If a subquery returns multiple rows, avoid using comparison operators such as = unless you are certain the result contains only one value.

⚠️ Best Practices

Best Practice

Use subqueries to simplify complex logic, but consider replacing expensive correlated subqueries with joins or Common Table Expressions (CTEs) when working with large datasets. Always ensure that the subquery returns the expected number of rows and columns, and use meaningful aliases for improved readability.

🚀 Key Points to Remember

📌 A subquery is a query inside another SQL query.
📌 The inner query executes before the outer query.
📌 Subqueries can be used in SELECT, FROM, WHERE, and HAVING.
📌 Use IN, EXISTS, ANY, and ALL with multi-row subqueries.
📌 Correlated subqueries execute once for each outer row.
📌 Joins or CTEs may offer better performance for complex queries.

>>"A subquery allows one query to build upon the result of another, making complex data retrieval both flexible and powerful."

Summary

✅ Subqueries are one of SQL's most versatile features, enabling you to solve complex problems by nesting queries inside other queries. Whether filtering records, calculating dynamic values, or comparing grouped data, subqueries are essential for advanced SQL programming and data analysis.