Subqueries and Nested Queries

Introduction

Subqueries and nested queries allow SQL queries to be more powerful by enabling one query to be embedded within another. This is useful for retrieving data that depends on the results of another query. In this tutorial, we will explore the concept, syntax, and various use cases of subqueries and nested queries.

What is a Subquery?

A subquery, also known as an inner query, is a query nested inside another SQL query. The outer query, or main query, depends on the results of the subquery to execute.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR (
    SELECT column_name
    FROM another_table
    WHERE condition
);

Types of Subqueries

1. Single-Row Subqueries

A subquery that returns only one row of data.

Example:

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

This query retrieves employees whose salary is higher than the average salary.

2. Multi-Row Subqueries

A subquery that returns multiple rows and is used with IN, ANY, or ALL.

Example:

SELECT name, department
FROM employees
WHERE department IN (
    SELECT department FROM employees WHERE salary > 50000
);

This query finds all employees in departments where at least one employee earns more than 50,000.

3. Correlated Subqueries

A correlated subquery is executed once for each row processed by the outer query.

Example:

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

This query retrieves employees earning more than the average salary in their respective departments.

4. Nested Queries in SELECT Clause

Subqueries can also be used in the SELECT clause to return computed values.

Example:

SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

This query displays each employee’s name along with the highest salary in the company.

5. Nested Queries in FROM Clause

Subqueries can be used as derived tables in the FROM clause.

Example:

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

This query finds departments where the average salary exceeds 60,000.

Performance Considerations

• Optimize subqueries: Use indexes and ensure efficient execution plans.
• Use JOINs when possible: Sometimes, JOINs perform better than subqueries.
• Avoid unnecessary subqueries: Reduce complexity by using alternative query structures.

Conclusion

Subqueries and nested queries add flexibility to SQL queries, allowing complex data retrieval scenarios. Understanding different types of subqueries and their use cases helps in writing efficient and effective SQL queries.