EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Subquery

Oracle Subquery

Priya Pedamkar
Article byPriya Pedamkar

Updated February 28, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

Types of Subquery with Example

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.

oracle subquery 1

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.

first 5 rows

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.

vehicle_ids

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.

oracle subquery 4

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.

Recommended Articles

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?
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1
 20+ Hours of HD Videos
2 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more