SQL Correlated Subquery
Summary: in this tutorial, you will learn about the SQL correlated subquery which is a subquery that uses values from the outer query.
Introduction to SQL correlated subquery
Let’s start with an example.
See the following employees
the table in the sample database:
The following query finds employees whose salary is greater than the average salary of all employees:
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary > (SELECT
AVG(salary)
FROM
employees);
In this example, the subquery is used in the WHERE
clause. There are some points that you can see from this query:
First, you can execute the subquery that returns the average salary of all employees independently.
SELECT
AVG(salary)
FROM
employees;
Second, the database system needs to evaluate the subquery only once.
Third, the outer query makes use of the result returned from the subquery. The outer query depends on the subquery for its value. However, the subquery does not depend on the outer query. Sometimes, we call this subquery is a plain subquery.
Unlike a plain subquery, a correlated subquery is a subquery that uses the values from the outer query. Also, a correlated subquery may be evaluated once for each row selected by the outer query. Because of this, a query that uses a correlated subquery may be slow.
A correlated subquery is also known as a repeating subquery or a synchronized subquery.
SQL correlated subquery examples
Let’s see a few more examples of the correlated subqueries to understand them better.
SQL correlated subquery in the WHERE
clause example
The following query finds all employees whose salary is higher than the average salary of the employees in their departments:
SELECT
employee_id,
first_name,
last_name,
salary,
department_id
FROM
employees e
WHERE
salary > (SELECT
AVG(salary)
FROM
employees
WHERE
department_id = e.department_id)
ORDER BY
department_id ,
first_name ,
last_name;
Here is the output:
In this example, the outer query is:
SELECT
employee_id,
first_name,
last_name,
salary,
department_id
FROM
employees e
WHERE
salary >
...
and the correlated subquery is:
SELECT
AVG( list_price )
FROM
products
WHERE
category_id = p.category_id
For each employee, the database system has to execute the correlated subquery once to calculate the average salary of the employees in the department of current employee.
SQL correlated subquery in the SELECT
clause example
The following query returns the employees and the average salary of all employees in their departments:
SELECT
employee_id,
first_name,
last_name,
department_name,
salary,
(SELECT
ROUND(AVG(salary),0)
FROM
employees
WHERE
department_id = e.department_id) avg_salary_in_department
FROM
employees e
INNER JOIN
departments d ON d.department_id = e.department_id
ORDER BY
department_name,
first_name,
last_name;
The output is:
For each employee, the database system has to execute the correlated subquery once to calculate the average salary by the employee’s department.
SQL correlated subquery with EXISTS operator example
We often use a correlated subquery with the EXISTS
operator. For example, the following query returns all employees who have no dependents:
SELECT
employee_id,
first_name,
last_name
FROM
employees e
WHERE
NOT EXISTS( SELECT
*
FROM
dependents d
WHERE
d.employee_id = e.employee_id)
ORDER BY first_name ,
last_name;
The following picture shows the output:
In this tutorial, you have learned about the SQL correlated subquery and how to apply it to form a complex query.
0 Comments
CAN FEEDBACK
Emoji