HomeOracle 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.
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 FROM ( 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.