Non-correlated vs Correlated Subqueries
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?
Obrigado pelo seu feedback!
Pergunte à IA
Pergunte à IA
Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo
Incrível!
Completion taxa melhorada para 5.56Non-correlated vs Correlated Subqueries
Deslize para mostrar o menu
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?
Obrigado pelo seu feedback!
