SQL Correlated Subquery

Correlated Subquery is a type of subquery that references one or more columns from the outer query in its WHERE clause or HAVING clause.

1. Overview

A Correlated Subquery is a subquery that depends on the outer query for its execution. Unlike a regular subquery, a correlated subquery can reference columns from the outer query, making it dependent on the context of the outer query's result set.

Example:

// Example of a correlated subquery used to find employees with salaries greater than the average salary of their department
SELECT employee_id, employee_name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

In this example, the correlated subquery references the department_id column from the outer query, allowing it to calculate the average salary for each department individually.

2. Syntax

The syntax for a Correlated Subquery is similar to that of a standard subquery, but it includes references to columns from the outer query:

SELECT column_name
FROM table_name outer_table
WHERE expression operator (SELECT column_name FROM table_name inner_table WHERE inner_table.column_name = outer_table.column_name);

Where the subquery references columns from the outer query.

3. Usage

Correlated Subqueries are commonly used when there is a need to perform row-by-row comparison or calculation based on values from the outer query. They are useful for filtering, aggregating, or performing calculations based on specific conditions related to the current row being processed.

Example:

// Example of using a correlated subquery to find customers who have made more orders than the average number of orders in their city
SELECT customer_id, customer_name
FROM customers c
WHERE (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) > (
    SELECT AVG(order_count)
    FROM (
        SELECT COUNT(*) AS order_count
        FROM orders
        GROUP BY city
    ) AS avg_orders
    WHERE avg_orders.city = c.city
);

This example correlates the subquery with the outer query to calculate the average number of orders per city and then compares it with the number of orders made by each customer in their respective cities.

4. Conclusion

Correlated Subqueries provide a powerful mechanism for performing row-by-row comparisons or calculations based on values from the outer query. By referencing columns from the outer query, correlated subqueries enable complex filtering, aggregation, and analysis of data within SQL queries.