This SQL tutorial focuses on Oracle Subqueries, and provides explanations, examples and exercises. For this lesson’s exercises use this link.
In general, a subquery is a query within another query, the subquery is used to return data that will be used in the main query. Subqueries can be used in various places within a query (such as: SELECT, FROM, WHERE), this tutorial explains how to use subqueries in the Oracle WHERE clause.
Using Oracle Subqueries
Subqueries are widely used to answer a question within another question. For example, which products cost more than product no. 54 ?
To retrieve this information, you need to answer two question, each in a separate query :
- What is product no. 54’s price?
SELECT price FROM products WHERE product_id = 54 -- Let's assume that this is the result : price ------ 62
- Which products cost more than product no. 54?
SELECT product_id, product_name, price FROM products WHERE price > 62
Instead of executing each query separately, you can combine the two queries, placing one query inside the other.
Basic Oracle Subquery Syntax
SELECT … FROM table WHERE condition (SELECT … FROM table)
Guidelines
SELECT product_id , product_name , price
FROM products
WHERE price > ( SELECT price
FROM products
WHERE product_id = 54)
- The subquery is executed once before the main query, then the result returned by the subquery is submitted to the main query (in the Oracle example provided above, the subquery determines the price of product no. 54, then the main query takes the result of that subquery and uses the result to display all the products that cost more than this amount).
- The subquery must be enclosed by round brackets.
- Place subqueries on the right side of the comparison condition.
- A subquery cannot be placed in the Oracle GROUP BY Clause.
- Sub-queries can be divided into two main categories :
- Single Row Subqueries – subqueries that return zero or one row to the outer SQL statement.
- Multiple Row Subqueries – subqueries that return more than one row to the outer SQL statement.
Oracle Single Row Subquery
You may use comparison operators (also referred as single-row operators) in the outer query to handle a subquery that returns a single value. This Oracle example would retrieve the products whose category number is the same as that of product 64.
SELECT product_name , product_id , price , category_id
FROM products
WHERE category_id = ( SELECT category_id
FROM products
WHERE product_id = 64)
- The subquery must return a single row; a subquery written without a Oracle WHERE Clause (hence usually returns more than one row) will generate an error.
- The subquery must return a single column; specifying more than one column in the subquery’s SELECT clause will result in an error.
- If you need to display all products whose category number is the same as that of product 54, excluding product 54, simply add this condition
- AND product_id <> 54
SELECT product_name , product_id , price , category_id
FROM products
WHERE category_id = (SELECT category_id
FROM products
WHERE product_id = 54)
AND product_id <> 54
- You can use group functions in a subquery to return a single row. For example: retrieve all products that cost more than the average price in category no. 60:
SELECT product_id , product_name , price
FROM products
WHERE price > ( SELECT AVG(price)
FROM products
WHERE category_id = 60)
- A subquery also can be used in the Oracle HAVING Clause. This Oracle example retrieves a summary of the average price for each category, for all categories whose average price is greater than the average price of category no. 90:
SELECT category_id , AVG(price)
FROM products
GROUP BY category_id
HAVING AVG(price) > (SELECT AVG(price)
FROM products
WHERE category_id = 90)
- The next Oracle example would retrieve all products that cost more than product no. 54 and their category number equals the category number of product no. 42, not including product no. 42. This Oracle example consists of three queries, main query and two subqueries. The subqueries are executed first, generating the query results. Then the main query is processed and uses the values returned by these subqueries.
SELECT product_id , product_name , price
FROM products
WHERE price > (SELECT price
FROM products
WHERE product_id = 54 )
AND category_id = (SELECT category_id
FROM products
WHERE product_id = 42)
AND product_id <> 42
Oracle Multiple Row Subquery
You may use the IN, ANY, or ALL operators (multiple row operators) in the outer query to handle a subquery that returns multiple rows, the multiple row operators expect one or more values.
The column below represents the prices of different products in category number 80, the following examples will use these values as the multiple row subquery result.
SELECT price FROM products WHERE category_id = 80
| price |
|
4300 |
|
5200 |
|
6700 |
|
8200 |
|
12500 |
Oracle IN Operator
The Oracle IN operator allows comparing a column with a list of values returned from the subquery. This Oracle example would retrieve all products whose price is equal to one of the prices of products in category 80:
SELECT product_id , product_name , price
FROM products
WHERE price IN (SELECT price
FROM products
WHERE category_id = 80)
- In fact, the main query would look like the following to the Database Server :
SELECT product_id , product_name , price FROM products WHERE price IN (4300,5200,6700,8200,12500)
- To display all products whose price is equal to one of the prices of products in category 80, excluding the products in category 80, simply use this condition : AND category_id <> 80.
SELECT product_id , product_name , price
FROM products
WHERE price IN (SELECT price
FROM products
WHERE category_id = 80)
AND category_id <> 80
Oracle ANY Operator
The Oracle ANY operator allows comparing a column with at least one of the values returned from the subequry. When using this operator, it is possible to work with the following comparison methods : >ANY, <ANY, =ANY
> ANY
The following Oracle example would retrieve all products whose price is greater than at least one of the prices of the products in category 80.
When seeking to know which value is greater than at least one of the values in a specific list, you actually seek to find the value that is greater than the minimum (because the requested value must be greater than at least one of the values in the list, no matter which value).
SELECT product_id , product_name , price
FROM products
WHERE price > ANY (SELECT price
FROM products
WHERE category_id = 80)
< ANY
The following Oracle example would retrieve all products whose price is lower than at least one of the prices of the products in category 80.
When seeking to know which value is less than at least one of the values in a specific list, you actually seek to find the value that is less than the maximum (because the requested value must be less than at least one of the values in the list, no matter which value).
SELECT product_id , product_name , price
FROM products
WHERE price < ANY (SELECT price
FROM products
WHERE category_id = 80)
= ANY
The following Oracle example would retrieve all products whose price is equal to at least one of the prices of products in category 80. =ANY is equivalent to IN
SELECT product_id , product_name , price
FROM products
WHERE price = ANY (SELECT price
FROM products
WHERE category_id = 80)
ALL Operator
The Oracle ALL operator allows comparing a column with all of the values returned from the subquery. When using this operator, it is possible to work with the following comparison methods: < ALL, >ALL.
> ALL
The followng Oracle example would retrieve all products whose price is greater than the prices of all products in category 80.
When seeking to find the value that is greater than all values in a certain list, you actually look for a value that is greater than the maximum (for a value to be greater than all of the values, it must necessarily be greater than the maximum value in the list of values).
SELECT product_id , product_name , price
FROM products
WHERE price > ALL (SELECT price
FROM products
WHERE category_id = 80)
< ALL
The following Oracle example would retrieve all products whose price is lower than all of the prices of all products in category 80.
When seeking to find the value that is less than all values in a certain list, you actually look for a value that is less than the minimum (for a value to be less than all of the values, it must necessarily be less than the minimum value in the list of values).
SELECT product_id , product_name , price
FROM products
WHERE price < ALL (SELECT price
FROM products
WHERE category_id = 80)
- The Oracle = ALL operator attempts to retrieve a value that equals to all of the values returned from the subquery (the product whose price is worth 4300, and also 5200, and also 6700, and also 8200 and also 12500). This condition seeks to carry out an illogical operation, and will therefore usually not be used.
