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

Designing sub-queries to deal with more than one table (or different tables) - 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.

  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



Let us consider that I would like to retrieve KING's department name.  All department names are available in the table “dept,” which is quite different from “emp” table.  Let us write the question in a meaningful manner and identify the steps as follows:

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

  • Find KING's department number (1).
  • Based on the value you get, retrieve the department details like dname, loc. etc belonging to that department number (2)

The following is the statement which retrieves KING's department number:

SELECT deptno FROM emp WHERE ename = ‘KING’

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

SELECT dname FROM dept

WHERE deptno = 30

Just replace the value 30 with the query that gives you KING's department number.  The complete statement would be as follows:

SQL> SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp WHERE ename=’KING’);

Now, let us walk through its execution:

  • The innermost query gets executed first. 
  • In this case, the query “select deptno from emp where ename=’king’” gets executed first.  It retrieves KING's department number.
  • Once the innermost query gets executed, it returns a value to the immediate outer query.  In this case, it is 10.
  • The entire innermost query gets replaced with the new value returned by it.  In this case, the outer-query virtually becomes “select dname from dept where deptno = 10.”
  • And finally, the outer query gets executed, which retrieves KING’s department details

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: