Home arrow Oracle arrow Page 4 - Inserting Sub-Queries in SELECT Statements in Oracle

Derived tables (or inline views) with Sub-Queries in Oracle - Oracle

This is the second article in a series concentrating on working with sub-queries in Oracle. My previous article (first in this series) explained the “dual” table in detail and gave a few practical examples of sub-queries. In this article, I shall introduce sub-queries at various interesting places within the same SELECT statement.

  1. Inserting Sub-Queries in SELECT Statements in Oracle
  2. Sub-Queries returning single and multiple values in Oracle
  3. Sub-Queries as part of the BETWEEN operator in Oracle
  4. Derived tables (or inline views) with Sub-Queries in Oracle
  5. Sub-Queries with CASE structure in Oracle SELECT statements
  6. Sub-Queries as (or part of) columns in Oracle SELECT statements
By: Jagadish Chatarji
Rating: starstarstarstarstar / 62
November 14, 2006

print this article



Can we write sub-queries in (or as part of) the FROM clause? The answer is YES and you can use it very efficiently in some scenarios. 

Let us consider the following statement:

SELECT empno,ename,sal,sal*12 AS AnnSal
FROM emp
WHERE AnnSal > 30000

If I execute the above statement, it will result in an error saying that “AnnSal” is an “Invalid Identifier.”  The reason is that “AnnSal” is not a column existing in the table “emp.”  It is simply an alias (or some reasonable name). We are not allowed to work with a column alias in any of the conditions present in WHERE clause.

Let us modify the above statement to make it work. Try the following now:

SELECT empno,ename,sal,AnnSal
      SELECT empno,ename,sal,sal*12 AS AnnSal
      FROM emp
WHERE AnnSal > 30000

The above statement is totally different from the previous one. Within the above statement, the outer query doesn’t rely on any specific physical table. The output (or result set) of the inner query is considered as a table for the outer query! The inner query is very similar to that of a view which doesn’t have any physical view name, and it gets created and destroyed on the fly.

So, according to the inner query, it retrieves four columns (empno, ename, sal, AnnSal). The outer query can work with all four columns as if they are directly from a physical table. 

As you are trying to define/derive your own table of information from an existing physical table, you call it as a derived table (or inline view). Even the derived tables can be nested to any number of levels with further sub-derived tables as part of FROM clauses.

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