Apprendre Correlated vs. Non-Correlated Subqueries in WHERE | Nested Subqueries in the WHERE section

Glissez pour afficher le menu

Understanding the difference between correlated and non-correlated subqueries is essential when working with nested queries in the WHERE clause. Both types of subqueries allow you to filter results based on dynamic conditions, but the way they interact with the outer query and their performance characteristics differ significantly.

A correlated subquery is a subquery that references columns from the outer query. This means that the subquery is evaluated once for each row processed by the outer query, as it depends on values from that specific row. You typically use correlated subqueries when you need to compare each row to a set of data that is related to that row, such as finding employees whose salary is above the average salary within their own department.

A non-correlated subquery, on the other hand, is independent of the outer query. It can be executed once and its result reused for every row in the outer query. Non-correlated subqueries are ideal when you want to compare each row to a fixed value or set of values, such as retrieving employees who earn more than the overall average salary across the entire company.

Choosing between these subquery types depends on the relationship between the data you are comparing. Use a correlated subquery when each row requires a different comparison value, and a non-correlated subquery when the comparison value is the same for all rows.


              123456789101112131415
            
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id
WHERE
    e.salary > (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    );

This correlated subquery finds employees whose salary is above the average salary of their department. The key aspect is that the inner query:

SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id

references e.department_id, which comes from the outer query's current row. For each employee, the subquery computes the average salary within that employee's department, and the outer query then checks if the employee's salary is greater than this department-specific average. This row-by-row comparison is what makes the subquery correlated.


              1234567891011
            
SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM
    employees
WHERE
    salary > (
        SELECT AVG(salary) FROM employees
    );

1. Which of the following best describes the key difference between a correlated and a non-correlated subquery?

2. When should you use a correlated subquery in the WHERE clause?

3. Examine the following query and identify which part makes it a correlated subquery:

SELECT
    e.employee_id,
    e.first_name,
    e.last_name
FROM
    employees e
WHERE
    e.salary > (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    );

Match the phrase in the subquery that references the outer query.

Examine the following query and identify which part makes it a correlated subquery:

SELECT
    e.employee_id,
    e.first_name,
    e.last_name
FROM
    employees e
WHERE
    e.salary > (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    );

Match the phrase in the subquery that references the outer query.

AVG(e2.salary)FROM employees e2e.salary > ...

Tout était clair ?

Merci pour vos commentaires !

Section 1. Chapitre 3

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Section 1. Chapitre 3