SQL Correlated Subquery

Summary: In this tutorial, you’ll learn how to use SQL correlated subquery to perform row-by-row comparisons and complex filtering.

Introduction to SQL correlated subquery #

A correlated subquery is a kind of subquery that depends on the outer query for its input values.

A regular query executes once and provides the result to an outer query. However, a correlated subquery executes repeatedly – once for each row processed by the outer query.

A correlated subquery has two main characteristics:

Referencing a column from the outer query.
Executing once for each row of the outer query.

Typically, you use a correlated subquery in WHERE, SELECT, or HAVING clauses.

SQL correlated subquery example #

We’ll use the employees table from the HR sample database:

SQL Correlated Subquery - employees table

The following example uses a correlated subquery to find employees whose salary is greater than the average salary of their department:

SELECT
  first_name,
  department_id,
  salary
FROM
  employees e1
WHERE
  salary > (
    SELECT
      AVG(salary) average_salary
    FROM
      employees e2
    WHERE
      e2.department_id = e1.department_id
  )
ORDER BY
  department_id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | department_id |  salary
------------+---------------+----------
 Michael    |             2 | 13000.00
 Den        |             3 | 11000.00
 Shanta     |             5 |  6500.00
 Matthew    |             5 |  8000.00
 Adam       |             5 |  8200.00
 Payam      |             5 |  7900.00
 Bruce      |             6 |  6000.00
 Alexander  |             6 |  9000.00
 John       |             8 | 14000.00
 Karen      |             8 | 13500.00
 Steven     |             9 | 24000.00
 Daniel     |            10 |  9000.00
 Nancy      |            10 | 12000.00
 Shelley    |            11 | 12000.00Code language: SQL (Structured Query Language) (sql)

How the query works.

The outer query selects an employee (e1) whose salary is greater than the average salary of all employees in the current employee’s department.

The subquery calculates the average salary of the current employee’s department (e2.department_id = e1.department_id).

The subquery is executed for each employee selected in the outer query.

Summary #

A correlated subquery is a query that uses values from the outer query.
A correlated subquery is executed for each row from the outer query.

Quiz #

Databases #

Was this tutorial helpful ?