An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery were a table name.
A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:
SQL> select * 2 from 3 -- Inline view starts 4 ( 5 select ename 6 ,sal 7 ,dense_rank() over (order by sal desc) dr 8 from emp 9 ) 10 -- Inline view ends 11 where dr = 4 -- Replace 4 with any value of N; ENAME SAL DR ---------- ---------- ---------- BLAKE 2850 4 CLARK 2850 4
Did you notice the structure of the queries to pick odd rows or even rows from a table? They use inline views too.
Inline views have the word "view" in them, they behave like views, but they are different from views in one crucial aspect: inline views are not database objects. They do not occupy storage.
Why use inline views? Why not just create the view?
Prior to the introduction of inline views in Oracle 7.1, we had no choice but to create a database view for queries like the Top-N query.
Creating a view implies another database object to maintain. Why have that, when all you need is query results on the fly?
By placing the view within the main SELECT, you have all the code needed for the query in one place.
Photo by Groume
{ 4 comments… read them below or add one }
Out of the ten sites I’ve checked on, this is the only explanation that finally made inline views click for me. Thank you.
Thanks nabokovian. Great to hear your feedback.
Nice explained.Appreciate the way it is interpreted with example.
This site explains difficult topics in a v. easy manner. Thank you very much.