Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Learning about Subqueries in Oracle

In this article, we will learn to define subqueries and how to use them.

Subqueries, as suggested by the name, are queries within a query. If we define a SQL statement having a query within a query then the outer query will be called as main query and the inner query will be called as a subquery. We can also think of a subquery as an advanced SELECT statement where we use another query (subquery) to specify the condition in the WHERE clause of the statement.

The general syntax for a subquery can be thought of as:

QUERY
	(SUBQUERY)

We can have different types of subqueries. A subquery can either be a single row subquery or a multiple row subquery. We can have a subquery defined within another subquery. Such subqueries are called as nested subqueries.

When to use subqueries?

We need to use subqueries when we want the result of one query to act as a search condition for another query. In this, the subquery is executed before the main query and the output of the subquery is used by the main query to evaluate itself.

For Example:

We want to write a query to find the names of all those employees who work in the same company as John’s.

We can express it as:

Main Query:

Names of all the employees where company is same as

	Subquery:
	Get the name of John’s company

While evaluating this query, firstly the name of John’s company will be evaluated and it will then be used to get the name of all the employees who are working in John’s company.

Syntax of a Subquery:

As we have already discussed, a subquery is basically a query (which is indeed a SELECT statement) placed under a SELECT statement. We can place the subquery at following places in a SELECT statement :

  • After FROM keyword
  • In WHERE condition
  • In HAVING clause

Mostly we use it to specify a value in the WHERE condition and has the following syntax:

SELECT <attribute name>
FROM <table>
WHERE <expression> operator <subquery>

Here:

  • SELECT is used to specify the name of columns we want to retrieve.
  • FROM is used to specify the name of table.
  • Where is used to specify the condition.

Operators are the comparison operators and can be of two types:

  • Single Row Operators: >, <, <=, >=, =, <>
  • Multiple Row Operators: IN, ANY, ALL

Suppose we have a table called Author in our database which looks like:

Author

AUTHOR_ID FIRST_NAME LAST_NAME PHONE BOOKS SALARY COMPANY DOB ADDRESS
1 John Doe 999123456 5 24000 ABC 16-Aug-63 Califrornia
5 Steve Brown 698296556 5 45000 ABC 29-Nov-70 Boston
7 Ben Stuart 877034486 4 15000 XYZ 11-Mar-68
4 April Berker 573902845 9 78900 ABC 30-Jun-50 London
2 Stefan Richardson 234551738 7 45000 XYZ 24-Oct-60
3 Elena Snow 890376186 5 20987 PQR 02-Feb-80 Austria
6 Mark Wright 905684728 10 100000 PQR 10-Jul-55 Chicago

Subquery Use

Step 1: In this step we will learn to use subqueries inside a main query.

Listing 1: SQL statement to get the names of all authors who work in John’s company.

SELECT First_Name || ' ' || Last_Name AS Name, Company, Salary
FROM Author
WHERE Company= (SELECT Company FROM Author WHERE First_Name='John')

When we run the above query, then we will get the information of all those authors who work in the same company in which John is working. We are retrieving the name of John’s company using the subquery and the output of subquery is used to evaluate the main query.

The output of the following query is as follows:

Output 1:

NAME COMPANY SALARY
John Doe ABC 24000
Steve Brown ABC 45000
April Berker ABC 78900
Output 1

Figure 1: Output 1

Single-Row Subqueries

Single-row subqueries are those subqueries which return only a single row for each subquery. We can use single-row subqueries with single-row comparison operators (, =, =,

Step 2: In this step we will learn to use Single-Row Subquery.

Listing 2: SQL statement to use multiple single row subqueries in one statement.

SELECT First_Name, Last_Name, Company, Books,Salary
FROM Author

WHERE Books >= (SELECT Books FROM Author WHERE Author_Id=7)

AND Salary >= (SELECT Salary FROM Author WHERE Author_Id=5)

When we run the above query, then we will get the information of all those authors who have written books more or equal to author with Author_Id=7 and have salary greater than or equal to author with Author_Id=5. We have used Id’s here because there can be only one author with a particular ID.

The output of the following query is as follows:

Output 2:

FIRST_NAME LAST_NAME COMPANY BOOKS SALARY
Steve Brown ABC 5 45000
April Berker ABC 9 78900
Stefan Richardson XYZ 7 45000
Mark Wright PQR 10 100000
Output 2

Figure 2: Output 2

Group Functions In Subquery

We can also use Group functions in a subquery and can use the value returned by it to evaluate the main query. Any Group function can be used inside a subquery but we must make sure that we are using the correct type of operators with the corresponding subquery.

Step 3: In this step we will learn to use Group functions in a subquery.

Listing 3: SQL statement to use AVG function in a subquery.

SELECT First_Name, Last_Name, Company, Books, Salary

FROM Author

WHERE Books > (SELECT AVG(Books) FROM Author)

When we run the above query, then we will get the information of all those authors who have written books more than the average books written by authors. We have used the AVG() group function here which is used to calculate the average.

The output of the following query is as follows:

Output 3:

FIRST_NAME LAST_NAME COMPANY BOOKS SALARY
April Berker ABC 9 78900
Stefan Richardson XYZ 7 45000
Mark Wright PQR 10 100000

Output 3

Figure 3: Output 3

Subquery In HAVING clause

We can also use subqueries in the HAVING clause of a SQL statement. The evaluation of the subquery will be similar to its evaluation in ‘WHERE’ condition.

Step 4: In this step we will learn to use subquery in HAVING clause

Listing 4: SQL statement to use subquery in HAVING clause

SELECT Company, MIN(Books)

FROM Author
GROUP BY Company

HAVING MIN(Books) > (SELECT MIN(Books) FROM Author)

When we run the above query, then we will get the name of those companies in which the number of books written by every author is more than the minimum books written by any author.

The output of the following query is as follows:

Output 4:

COMPANY MIN(BOOKS)
PQR 5
ABC 5

Output 4

Figure 4: Output 4

Multiple-Row Subqueries

Multiple-row subqueries are those subqueries which return more than one row for each subquery. We can use multiple-row subqueries with multiple-row comparison operators (IN, ANY, ALL) only.

Step 5: In this step we will learn to use Multiple-Row Subquery.

Listing 5: SQL statement to use multiple row subquery to get different Author_Ids based on a condition.

SELECT Author_Id, First_Name, Last_Name, Books,Salary

FROM Author

WHERE Author_Id IN (SELECT Author_Id FROM Author WHERE Salary >40000)

When we run the above query, then we will get the information of all those authors who have salary more than 40000. The subquery will return more than one row here.

The output of the following query is as follows:

Output 5:

AUTHOR_ID FIRST_NAME LAST_NAME BOOKS SALARY
5 Steve Brown 5 45000
4 April Berker 9 78900
2 Stefan Richardson 7 45000
6 Mark Wright 10 100000
Output 5

Figure 5: Output 5

Subqueries With Multiple-Row Operators

There are three multiple-row comparison operators. These are:

  • IN : It is used to check value with any member in the list.
  • ANY: It is used to compare to each value obtained in subquery.
  • ALL : It is used to compare to every value obtained in subquery.

We have discussed an example of IN operator. Now we will be discussing ANY and ALL operators.

Step 6: In this step we will learn to use subqueries with multiple-row operators.

Listing 6: SQL statement to use ANY operator with a subquery.

SELECT First_Name, Last_Name, Books, Company, Salary

FROM Author

WHERE Books > ANY(SELECT Books FROM Author WHERE Company='PQR')

When we run the above query, then we will get the information of all those authors who have written more books than any of the author working in ‘PQR’ company.

The output of the following query is as follows:

Output 6:

FIRST_NAME LAST_NAME BOOKS COMPANY SALARY
Mark Wright 10 PQR 100000
April Berker 9 ABC 78900
Stefan Richardson 7 XYZ 45000
Output 6

Figure 6: Output 6

Listing 7 : SQL statement to use ALL operator with a subquery.

SELECT First_Name, Last_Name, Books, Company, Salary

FROM Author

WHERE Books > ALL(SELECT Books FROM Author WHERE Company='XYZ')

When we run the above query, then we will get the information of all those authors who have written more books than every author working in ‘XYZ’ company.

The output of the following query is as follows:

Output 7:

FIRST_NAME LAST_NAME BOOKS COMPANY SALARY
April Berker 9 ABC 78900
Mark Wright 10 PQR 100000

Output 7

Figure 7: Output 7

Nested Subqueries

We can also nest more than one subquery within other subquery. The nesting can be done up to 255 levels. Such subqueries are called as nested subqueries.

Step 8: In this step we will learn to use nested subqueries.

Listing 8: SQL statement to use nested subquery up to two levels.

SELECT First_Name, Last_Name, Books, Company, Salary

FROM Author

WHERE Books > ANY(SELECT Books FROM Author WHERE Company=(SELECT Company FROM Author WHERE Author_Id=4))

When we run the above query, then we will get the information of all those authors who have written more books than any of the author working in company in which author with Author_Id=4 is working.

The output of the following query is as follows:

Output 8:

FIRST_NAME LAST_NAME BOOKS COMPANY SALARY
Mark Wright 10 PQR 100000
April Berker 9 ABC 78900
Stefan Richardson 7 XYZ 45000
Output 8

Figure 8: Output 8

Conclusion:

In this article, we have learnt about the subqueries and how to use them. We have learned how to use single-row and multiple-row subqueries with different types of operators and how we can nest them within each other.

See you next time!



Have experience in Oracle, Java and have done certified courses in Android

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login