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

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.