SQL Subquery

Summary: In this tutorial, you’ll learn how to use SQL subqueries to form flexible queries for retrieving data from the database.

Introduction to SQL subquery #

A subquery is an SQL query nested inside another query. The query that contains a subquery is known as an outer query.

To write a subquery, you need to have a deep understanding of the SELECT statement:

SELECT
  select_list
FROM
  table1
  INNER JOIN table2 ON join_condition
WHERE
  filter_condition;Code language: SQL (Structured Query Language) (sql)

Note that the join can be INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN.

In this syntax:

The SELECT clause can accept a single value, which can be a column or an expression.
The FROM and INNER JOIN clauses can accept a result set such as a table.
The WHERE can accept a single value, which can be a column or an expression.

Based on the shape of the data each clause accepts, you can embed the appropriate subquery:

The subquery in the SELECT clause can return a single value.
The subquery in the FROM or INNER JOIN clauses can return a result set.
The subquery in the WHERE clause can return a single value.

SQL subquery in the WHERE clause #

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

The following statement uses a subquery to find the employees who have the highest salary:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary = (
    SELECT
      MAX(salary)
    FROM
      employees
  );Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name |  salary
------------+----------
 Steven     | 24000.00Code language: SQL (Structured Query Language) (sql)

How the query works.

First, the subquery returns the max salary from the salary column of the employees table:

SELECT
  MAX(salary)
FROM
  employeesCode language: SQL (Structured Query Language) (sql)

Second, the outer query uses the value returned by the subquery and returns the employee with the highest salary.

The following example uses a subquery to find employees with a salary greater than the average salary:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary > (
    SELECT
      AVG(salary)
    FROM
      employees
  )
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name |  salary
------------+----------
 John       |  8200.00
 Adam       |  8200.00
 William    |  8300.00
 Jack       |  8400.00
 Jonathon   |  8600.00
...Code language: SQL (Structured Query Language) (sql)

SQL Subquery with the IN operator #

The IN operator returns true if a value equals any value in a list of values. You can use a subquery to return a list of values for the IN operator:

IN subqueryCode language: SQL (Structured Query Language) (sql)

For example, the following query uses a subquery with the IN operator to find all employees with the job titles related to Sales:

SELECT
  first_name,
  last_name
FROM
  employees
WHERE
  job_id IN (
    SELECT
      job_id
    FROM
      jobs
    WHERE
      job_title LIKE '%Sales%'
  );Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name
------------+------------
 John       | Russell
 Karen      | Partners
 Jonathon   | Taylor
 Jack       | Livingston
 Kimberely  | Grant
 Charles    | JohnsonCode language: SQL (Structured Query Language) (sql)

How the query works.

First, the subquery returns a list of job IDs with the job titles have the word "Sales":

SELECT
  job_id
FROM
  jobs
WHERE
  job_title LIKE '%Sales%'Code language: SQL (Structured Query Language) (sql)

Output:

 job_id
--------
     15
     16Code language: SQL (Structured Query Language) (sql)

Second, the outer query selects the employees with the job_id in the job id list (15, 16).

Subquery in the SELECT clause #

The following example uses a subquery in the SELECT clause to retrieve the first name, salary, and average salary of all employees:

SELECT
  first_name,
  salary,
  (
    SELECT
      ROUND(AVG(salary),2) average_salary
    FROM
      employees
  )
FROM
  employees
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

 first_name  |  salary  | average_salary
-------------+----------+----------------
 Karen       |  2500.00 |        8060.00
 Guy         |  2600.00 |        8060.00
 Irene       |  2700.00 |        8060.00
 Sigal       |  2800.00 |        8060.00
 Shelli      |  2900.00 |        8060.00
...Code language: SQL (Structured Query Language) (sql)

Subquery in the FROM clause #

The following example shows how to use a subquery in the FROM clause:

SELECT
  ROUND(AVG(department_salary), 0) average_department_salary
FROM
  (
    SELECT
      department_id,
      SUM(salary) department_salary
    FROM
      employees
    GROUP BY
      department_id
  );Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 average_department_salary
---------------------------
                     29309Code language: SQL (Structured Query Language) (sql)

How it works.

First, the subquery returns a result set that includes department_id and total salary for each department:

SELECT
  department_id,
  SUM(salary) department_salary
FROM
  employees
GROUP BY
  department_id;Code language: SQL (Structured Query Language) (sql)

Second, the outer query calculates the average total salary of all departments and rounds it off with zero decimal places.

Subquery in the INNER JOIN clause #

The following example uses a subquery in the INNER JOIN clause of the outer query to retrieve employees who earn above the company’s average salary:

SELECT
  first_name,
  last_name,
  salary,
  s.avg_salary
FROM
  employees e
  INNER JOIN (
    SELECT
      ROUND(AVG(salary), 0) AS avg_salary
    FROM
      employees
  ) s ON e.salary > s.avg_salary
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  |  salary  | avg_salary
------------+------------+----------+------------
 John       | Chen       |  8200.00 |       8060
 Adam       | Fripp      |  8200.00 |       8060
 William    | Gietz      |  8300.00 |       8060
 Jack       | Livingston |  8400.00 |       8060
 Jonathon   | Taylor     |  8600.00 |       8060
...Code language: SQL (Structured Query Language) (sql)

How the query works.

First, the subquery calculates the company’s average salary.
Second, the outer query retrieves employees earning above that average salary.

Summary #

A subquery is a query nested in an outer query.
Embed an appropriate subquery in the SELECT, FROM, WHERE, and INNER JOIN clauses of a query.

Quiz #

Databases #

Was this tutorial helpful ?