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
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
SELECT MAX( list_price ) FROM products;
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;
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 );
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 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
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;
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.
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;
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;
In this statement:
- First, the subquery returns the list of
order_valuesorted by the
order_valuein 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;
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
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 uses of them.
See the following
order_items tables from the sample database.
For example, the following query finds the salesman who has 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;
Oracle evaluates this query in two steps:
- First, the subquery returns a list of the salesman whose sales is greater than or equal to 1 million.
- Second, the outer query uses the salesman id list to query data from the
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;
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 id 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.