Home arrow Oracle arrow Page 5 - Stepping through Sub-Queries in Oracle

An example of a nested sub-query (or multi-level sub-query) - Oracle

This is the first article in a series concentrating on working with sub-queries in Oracle. Sub-queries really have tremendous depth. In this series I will show you several scenarios where they can be efficiently used to retrieve information from Oracle databases.

TABLE OF CONTENTS:
  1. Stepping through Sub-Queries in Oracle
  2. The simplest sub-query in Oracle
  3. A sub-query with aggregate functions (or group functions) in Oracle
  4. Designing sub-queries to deal with more than one table (or different tables)
  5. An example of a nested sub-query (or multi-level sub-query)
By: Jagadish Chatarji
Rating: starstarstarstarstar / 28
November 07, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Let us consider that I would like to retrieve the department name of the highest paid employee. Let us write the question in a meaningful manner and identify the steps as follows:

From the above figure, you have three steps to go through with the query.  The following is the order you must follow (based on the above figure):

  • Find the highest salary (1).
  • Based on the value you get, retrieve the department number belonging to that salary (2).
  • Based on the value you get, find the department name belonging to that department number (3).

The following is the statement which finds the highest salary:

SELECT MAX(sal) FROM emp

To retrieve a department number based on a given salary (say 2000), the query would be as follows:

SELECT deptno FROM emp

WHERE sal = 2000

To retrieve a department name based on a given department number (say 20), the query would be as follows:

SELECT dname FROM dept

WHERE deptno = 20

Combining all of the above queries according to the order given above, the new query would be as follows:

SQL> SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp

WHERE sal = (SELECT MAX(sal) FROM EMP));

You can observe the following figure to understand how the execution takes place.  You can also observe the underlined columns on how they relate logically:

Now, let us walk through its execution:

  • The innermost query gets executed first. 
  • In this case, the query “select max(sal) from emp” gets executed first.  It retrieves the highest salary. In this case it would be 5000.
  • The entire innermost query gets replaced with the new value returned by it.  In this case, the immediate outer query virtually becomes “select deptno from emp where sal = 5000.”  Once this query gets executed, it returns a department number, which is 10 in this case.
  • And finally, the outermost query virtually becomes “select dname from dept where deptno = 10,” which retrieves KING’s department details.

Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: