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

The simplest sub-query in Oracle - 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



Before explaining sub-queries, let us consider the following scenario:

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE sal = 5000;

EMPNO     ENAME      SAL         DEPTNO                

--------- ---------- ----------- ------------

7839      KING       5000        10                    

1 rows selected

Let us modify the above statement as follows:

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE sal = (SELECT 5000 FROM dual);

I already explained the “dual” table in the previous section.  In the above statement, I have two SELECT statements as part of a single command.  The following is the order in which the above statement gets executed:

  • The innermost query gets executed first. 
  • In this case, the query “select 5000 from dual” gets executed first.
  • Once the innermost query gets executed, it returns a value to the immediate outer query.  In this case, it is 5000.
  • The entire innermost query gets replaced with the new value returned by it.  In this case, the outer query virtually becomes “select empno, ename, sal, deptno from emp where sal = 5000.”
  • And finally, the outer query gets executed, which retrieves KING’s details.

In the above case, I used a SELECT query as part of another SELECT query; thus it is called a “sub-query.”  You can even modify the above statement to include an expression as follows:

SQL> SELECT empno,ename,sal,deptno FROM emp

WHERE sal = (SELECT 2000+3000 FROM dual);

The above statement first evaluates “2000+3000” (which results in 5000) as part of executing the inner query.  Based on the returned value (which is 5000), the outer query gets executed.

The next section will show you a few simple and practically used sub-queries.

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