Correlated Subquery

Correlated Subquery

Introduction-

 

A correlated subquery is a SQL query in which the values of the inner query (subquery) are derived from the outer query. Essentially, the subquery is conducted many times, one for each row processed by the outer query. This makes correlated subqueries very useful for complex data retrieval scenarios, but they can also be more performance-intensive than non-correlated subqueries due to repeated execution.

 

Here's a step-by-step explanation to understand correlated subqueries in a structured way:

1. Basic Structure-

A correlated subquery typically looks something like this:

 

SELECT column_name(s)

FROM table_name AS outer_table

WHERE column_name OPERATOR

    (SELECT column_name

     FROM table_name AS inner_table

     WHERE outer_table.column = inner_table.column);

 

2. Key Components

Outer Query-

This is the main query that first gets executed up to the point of the subquery. It retrieves data from the outer table.

 

Inner Query (Subquery)-

This query is nested inside the outer query. Unlike a regular subquery, it references columns from the outer query, creating a dependency.

 

Correlation-

The correlation happens through the condition that links the inner and outer queries (e.g., outer_table.column = inner_table.column). This condition is what makes the subquery "correlated" because it must reference a column from the outer query for each row it processes.

 

3. Execution Flow-

The outer query starts executing.

For each row in the outer query, the inner (correlated) subquery executes.

The subquery uses a value from the current row of the outer query to perform its operation.

The result of the subquery is used to complete the operation of the outer query for the current row.

Steps 2-4 repeat for each row processed by the outer query.

 

4. Use Cases

Correlated subqueries are useful for-

Row-by-row operations-

When you need to perform calculations or comparisons on each row individually.

 

Existence checks-

To check if some related data exists in another table for each row.

 

Aggregate calculations-

Such as finding the minimum, maximum, average, etc., of grouped data specific to each row in the outer query.

 

 

5. Performance Considerations-

Since the subquery maybe executed once for each row in the outer query, correlated subqueries can be slow, especially on large datasets. It's often recommended to look for

alternatives, such as JOINs or temporary tables, if performance becomes an issue.

 

Example

Consider two tables, employees and department, where you want to find the names of employees who earn more than the average salary in their respective departments-

 

SELECT e.name, e.salary

FROM employees e

WHERE e.salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

 

In this example, for each employee in the outer query, the subquery calculates the average salary of their department and checks if their salary is above this average. The correlation between the outer and inner query is established through department_id.