Oracle Subquery

Summary: in this tutorial, you will learn about the Oracle subqueries that construct more readable queries and allows you to write queries without using complex joins or unions.

Introduction to the Oracle subquery #

A subquery is a SELECT statement nested inside another statement such as SELECT, INSERT, UPDATE, or DELETE. Typically, you can use a subquery anywhere you use an expression.

Consider this following subquery example that uses the products table from the sample database.

The following query uses the MAX() function to return the highest list price from the products table:

SELECT
  MAX(list_price)
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

To select the detailed information of the most expensive products, you use the list price above (8867.99) in the following query:

SELECT
  product_id,
  product_name,
  list_price
FROM
  products
WHERE
  list_price = 8867.99;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we need to execute two queries separately to get the most expensive product information.

By using a subquery, we can nest the first query inside the second one as shown in the following statement:

SELECT
  product_id,
  product_name,
  list_price
FROM
  products
WHERE
  list_price = (
    SELECT
      MAX(list_price)
    FROM
      products
  );Code language: SQL (Structured Query Language) (sql)

Try it

In this example, the query that retrieves the max price is called the subquery and the query that selects the detailed product data is called the outer query.

We say that the subquery is nested within the outer query. Note that a subquery must appear within parentheses ().

Oracle evaluates the whole query above in two steps:

• First, execute the subquery.
• Second, use the result of the subquery in the outer query.

If the subquery returns no row, the condition in the WHERE clause become false that causes the whole query returns an empty result set.

A subquery which is nested within the FROM clause of the SELECT statement is called an inline view.

Note that other RDBMS such as MySQL and PostgreSQL use the term derived table instead of the inline view.

A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery.

A subquery can contain another subquery. Oracle allows you to have an unlimited number of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels in the WHERE clause.

Advantages of Oracle subqueries #

These are the main advantages of subqueries:

• Provide an alternative way to query data that would require complex joins and unions.
• Make the complex queries more readable.
• Allow a complex query to be structured in a way that it is possible to isolate each part.

Oracle subquery in the FROM clause #

A subquery in the FROM clause of a SELECT statement is called an inline view, which has the following syntax:

SELECT * FROM (subquery) [AS] inline_view;Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the top 10 orders with the highest values:

SELECT
  order_id,
  order_value
FROM
  (
    SELECT
      order_id,
      SUM(quantity * unit_price) order_value
    FROM
      order_items
    GROUP BY
      order_id
    ORDER BY
      order_value DESC
  );Code language: SQL (Structured Query Language) (sql)

Try it

In this statement:

• First, the subquery returns the list of order_id and order_value sorted by the order_value in descending order.
• Then, the outer query retrieves rows from the list.

Oracle subquery with comparison operators #

The subqueries that use comparison operators e.g., >, >=, <, <=, <>, = often include aggregate functions, because an aggregate function returns a single value that can be used for comparison in the WHERE clause of the outer query.

For example, the following query finds products whose list price is greater than the average list price.

SELECT
  product_id,
  product_name,
  list_price
FROM
  products
WHERE
  list_price > (
    SELECT
      AVG(list_price)
    FROM
      products
  )
ORDER BY
  product_name;Code language: SQL (Structured Query Language) (sql)

Try it

This query works as follows:

• First, the subquery returns the average list price of all products.
• Second, the outer query gets the products whose list price is greater than the average list price returned by the subquery.

Oracle subquery with IN and NOT IN operators #

The subquery that uses the IN operator often returns a list of zero or more values. After the subquery returns the result set, the outer query makes use of them.

See the following employees, orders, and order_items tables from the sample database.

For example, the following query finds the salesman who had sales above 100K in 2017:

SELECT
  employee_id,
  first_name,
  last_name
FROM
  employees
WHERE
  employee_id IN (
    SELECT
      salesman_id
    FROM
      orders
      INNER JOIN order_items USING (order_id)
    WHERE
      status = 'Shipped'
    GROUP BY
      salesman_id,
      EXTRACT(
        YEAR
        FROM
          order_date
      )
    HAVING
      SUM(quantity * unit_price) >= 1000000
      AND EXTRACT(
        YEAR
        FROM
          order_date
      ) = 2017
      AND salesman_id IS NOT NULL
  )
ORDER BY
  first_name,
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Oracle evaluates this query in two steps:

• First, the subquery returns a list of the salesmen whose sales are greater than or equal to 1 million.
• Second, the outer query uses the salesman id list to query data from the employees table.

The following statement finds all customers who have not yet placed an order in 2017:

SELECT
  name
FROM
  customers
WHERE
  customer_id NOT IN (
    SELECT
      customer_id
    FROM
      orders
    WHERE
      EXTRACT(
        YEAR
        FROM
          order_date
      ) = 2017
  )
ORDER BY
  name;Code language: SQL (Structured Query Language) (sql)

Try it

In this statement:

• First, the subquery returns a list of IDs of customers who placed one or more orders in 2017.
• Second, the outer query returns the customers with the IDs that are not in the list returned by the subquery.

Oracle subquery in the SELECT clause #

The following statement returns the product name, list price, and the average list prices of products according to their categories:

SELECT
  product_name,
  list_price,
  ROUND(
    (
      SELECT
        AVG(list_price)
      FROM
        products p1
      WHERE
        p1.category_id = p2.category_id
    ),
    2
  ) avg_list_price
FROM
  products p2
ORDER BY
  product_name;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we used a subquery in the SELECT clause to get the average product’s list price. Oracle evaluates the subquery for each row selected by the outer query.

This subquery is called a correlated subquery which we will cover in detail in the next tutorial.

Summary #

• Use Oracle subqueries to construct more readable queries without using complex joins or unions.

Quiz #