Learn 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?

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain why this query is considered a correlated subquery?

What would the query look like if I wanted to compare salaries to the company-wide average instead?

Can you provide more examples of when to use correlated vs non-correlated subqueries?

Swipe to show 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?

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1