Oracle Subquery

Summary: in this tutorial, you will learn about the Oracle subquery that helps you 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 that 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)

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)

As you can see, 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)

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.

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 examples

A) Oracle subquery in the SELECT clause example

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)

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.

B) Oracle subquery in the FROM clause example

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
    )
FETCH FIRST 10 ROWS ONLY; Code language: SQL (Structured Query Language) (sql)

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 the first 10 rows from the top of the list.

C) Oracle subquery with comparison operators example

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)

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.

D) 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)

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)

In this statement:

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

In this tutorial, you have learned about the Oracle subquery which gives you an alternative way to construct more readable queries without using complex joins or unions.