Impara Non-correlated vs Correlated Subqueries | Subqueries in Human Resources

To work effectively with HR data in SQL, you need to understand the difference between non-correlated and correlated subqueries. Both types of subqueries allow you to answer complex questions, but they work in distinct ways.

A non-correlated subquery is independent of the outer query. It runs once, and its result is used by the main (outer) query. For example, if you want to find all employees whose salary is higher than the average salary across the entire company, you could use a non-correlated subquery. The subquery calculates the average salary once, and the outer query compares each employee's salary to that value.

A correlated subquery, on the other hand, depends on the outer query for its values. It runs once for each row processed by the outer query, using a value from the outer query as a parameter. This is useful when you need to compare each row to a group that is related to that row. In HR scenarios, you might want to compare an employee's salary to the average salary in their own department, not the whole company. Here, the subquery must recalculate the average for each department as the outer query examines each employee.


              1234567891011121314
            
-- Find employees whose salary is above the average salary in their department
SELECT
    e.employee_id,
    e.name,
    e.salary,
    e.department_id
FROM
    employees e
WHERE
    e.salary > (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    );

1. Which of the following SQL queries contains a correlated subquery?

2. When should you use a correlated subquery instead of a non-correlated subquery?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 1

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Scorri per mostrare il menu


              1234567891011121314
            
-- Find employees whose salary is above the average salary in their department
SELECT
    e.employee_id,
    e.name,
    e.salary,
    e.department_id
FROM
    employees e
WHERE
    e.salary > (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    );

1. Which of the following SQL queries contains a correlated subquery?

2. When should you use a correlated subquery instead of a non-correlated subquery?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 1