Oracle Subquery

Introduction to Oracle Subquery

Oracle subquery is a SELECT statement that is written inside another statement or query (We can say it a nested query or a query which is present within a query) which can be SELECT, INSERT, UPDATE or even DELETE statement and generally, these sub-queries reside inside the WHERE clause, the FROM clause or the SELECT clause so that the user can better construct and create more complex queries with the help of subqueries without even using joins or unions to write complex queries.

Syntax:

Subquery is a SELECT statement.

select column/columns from t1 where column1 = (select column1 from t2);

Parameters:

• t1: It refers to table from where we want to extract the data
• t2: It refers to the table from where the inner query gets the column2 value.
• Column1: Column on which WHERE condition is applied.

Working of Oracle Subquery

• Whenever we write a subquery, the subquery should be enclosed within the brackets or parentheses (). So, when the oracle gets a query which has also a subquery, it first executes the subquery and retrieves the result and then uses the same result in the outer query.
• When we use the subquery within the FROM clause of the SELECT query Oracle refers it as inline view and when the subquery is present in the WHERE clause of the SELECT statement, Oracle refers to it as a nested subquery.
• One important point to know is that there is no restriction in the number of subquery levels one can have in case of the FROM clause or inline view.

Types of Subquery with Example

Given below are the types of Subquery with examples:

1. Subquery with SELECT clause

In this case, as the name suggests the subquery will be present in the SELECT clause. In this case, Oracle first evaluates the subquery and then executes the outer query. One thing we should keep in mind is that we should use aggregate functions like COUNT, MAX, MIN in the select clause subquery so that the subquery returns a single value.

Example:

Code:

SELECT
vehicle_name,
city,
ROUND(
(
SELECT
AVG( age )
FROM
employee e1
WHERE
e1. employee_id = v2.EMPLOYEE_ID
),
2
) age, v2.employee_id
FROM
vehicle v2 WHERE v2.employee_id IS NOT NULL;

If we see the above example we have used subquery with the select clause. In this example we are using two tables’ vehicle and employee. The subquery is used to get the average age of the employees based on the condition as shown in the above query. Oracle evaluates the subquery for each row selected by the outer query which in this case is that employee id from vehicle table is not null.

Let us run the query in SQL developer and look at the output.

As you can see the average age is rounded off and shown.

2. Subquery in FROM clause

In this case the subquery is written in the FROM keyword. This is also called as inline view because it replaces a table in the query. We will see the below example to learn more about this.

Example:

Code:

SELECT
employee_id,
vehicle_name,
age
FROM (
SELECT e1.employee_id,
e1.vehicle_name,
e1.age
FROM
employee e1
WHERE
age >28
)
WHERE rownum <=5;

If we see the above example we can see that the inner query first returns the age which is greater than 26 from the employee table and then the outer query then retrieves the first 5 rows of the inner query result set.

Let us run the query in SQL developer and look the output.

As we can see the output shows the first five rows of the result set we got from the inner query.

3. Subquery with IN Operator

In this case we use IN operator with the subquery, the subquery can return one or more values in its result set due to which we use IN operator. The same result set is then used by the outer query.

Example:

Code:

SELECT EMPLOYEE_ID, name
FROM employee
WHERE vehicle_id IN (
SELECT VEHICLE_ID
FROM vehicle
WHERE VEHICLE_NAME ='TATA'
);

If we look at the above example the inner query executes first and it gets the vehicle_ids of the vehicles which are made by TATA from the vehicle table and then the outer query uses the vehicle_ids returned by the inner query to query data from the employee table.

Let us execute the above query in SQL developer and look at the output.

As we can see that the query is executed successfully and it returns two records.

4. Subquery with NOT IN Operator

In this case we use NOT IN operator with the subquery, The subquery can return one or more values in its result. The same result set is then used by the outer query.

Code:

SELECT EMPLOYEE_ID, name
FROM employee
WHERE vehicle_id NOT IN (
SELECT VEHICLE_ID
FROM vehicle
WHERE VEHICLE_NAME ='TATA'
);

If we look it works the same way as IN operator discussed earlier. The inner query executes first and it gets the vehicle_ids of the vehicles which are NOT made by TATA from the vehicle table and then the outer query uses the vehicle_ids returned by the inner query to query data from the employee table.

Let us execute the above query in SQL developer and look at the output.

As we can see that the query is executed successfully and it returns nine rows which means the inner query returned nine vehicle_ids from the vehicle table.

Conclusion

In this article, we have discussed the definition of a subquery and its syntax. We discussed how actually a subquery works and then also the types of subqueries which are used along with appropriate examples to understand better.

This is a guide to Oracle Subquery. Here we discuss the introduction, working of oracle subquery, types of Subquery along with respective example. You may also have a look at the following articles to learn more –

1. Oracle UNION ALL
2. UNION in Oracle
3. Table in MySQL
4. MINUS in Oracle
5. Guide to Oracle Window Functions
6. Oracle While Loop
7. Oracle CARDINALITY | How to Work?