Nested queries, also known as subqueries, are queries that are embedded within another SQL query. They allow you to perform complex queries by using the results of one query as the input for another. Nested queries can be used in various parts of an SQL statement, such as in the SELECT, WHERE, and FROM clauses.

Types of Nested Queries

1. Subquery in the SELECT Clause
2. Subquery in the WHERE Clause
3. Subquery in the FROM Clause
4. Correlated Subqueries

1. Subquery in the SELECT Clause

A subquery in the SELECT clause allows you to compute a value that will be returned in the result set of the main query.

Syntax:

SELECT column1,
       (SELECT aggregate_function(column2)
        FROM table2
        WHERE condition) AS alias_name
FROM table1
WHERE condition;

Example:

Retrieve employees and their maximum salary in their department:

SELECT employee_name,
       (SELECT MAX(salary)
        FROM employees e2
        WHERE e2.department_id = e1.department_id) AS max_department_salary
FROM employees e1;

In this example, the subquery calculates the maximum salary for each department.

2. Subquery in the WHERE Clause

A subquery in the WHERE clause is used to filter records based on the results of another query. This is useful for comparing values against a set of results.

Syntax:

SELECT column1, column2, ...
FROM table1
WHERE column1 operator (SELECT column
                        FROM table2
                        WHERE condition);

Example:

Find employees whose salary is higher than the average salary:

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

In this example, the subquery calculates the average salary, and the main query retrieves employees with a salary above this average.

3. Subquery in the FROM Clause

A subquery in the FROM clause is also known as a derived table. It allows you to use the result of a subquery as if it were a table.

Syntax:

SELECT column1, column2, ...
FROM (SELECT column1, column2
      FROM table2
      WHERE condition) AS derived_table
WHERE derived_table.column1 = value;

Example:

Find departments with more than 10 employees:

SELECT department_id, employee_count
FROM (SELECT department_id, COUNT(*) AS employee_count
      FROM employees
      GROUP BY department_id) AS dept_summary
WHERE dept_summary.employee_count > 10;

In this example, the subquery calculates the number of employees per department, and the main query filters out departments with fewer than 10 employees.

4. Correlated Subqueries

A correlated subquery is a subquery that references columns from the outer query. It is evaluated once for each row processed by the outer query.

Syntax:

SELECT column1, column2, ...
FROM table1 t1
WHERE column1 operator (SELECT column2
                        FROM table2 t2
                        WHERE t2.column3 = t1.column4);

Example:

Find employees who earn more than the average salary in their department:

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

In this example, the subquery is correlated with the outer query through e1.department_id, allowing for a comparison of salaries within each department.

Key Points

• Performance: Nested queries can sometimes be less efficient than joins, especially if the subqueries are complex or if they are executed many times. Always consider indexing and query optimization.
• Readability: Nested queries can make SQL statements more complex and harder to read. Use them judiciously and ensure that they are well-documented.
• Use Cases: Subqueries are useful for scenarios where complex conditions are needed, or when dealing with aggregate functions or comparisons against dynamic sets of data.