Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pravin .

Asked: August 14, 2016 - 6:32 am UTC

Last updated: August 16, 2016 - 1:13 am UTC

Version: 10g

Viewed 1000+ times

You Asked


When we can use any query with ''From '' clause, can we call it subquery???

eg. select * from (select * from emp order by sal desc);

and Connor said...

The common terminology for this is not "subquery", it is an "inline view"

Subquery
========
select ...
from   my_table
where col = (  select   ...  )   <==== subquery

select ...
from   my_table
where col in (  select   ...  )   <==== subquery

Inline View
========
select *
from  ( select .... )   <==== inline view








Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 15, 2016 - 5:30 am UTC


regarding subquery terminology

Mikhail Velikikh, August 15, 2016 - 7:27 am UTC

Just to add to what Connor has said:

@ When we can use any query with ''From '' clause, can we call it subquery???

According to Oracle Documentation, we can:

https://docs.oracle.com/database/121/SQLRF/queries001.htm#SQLRF52327

A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level SELECT statement is called a query, and a query nested within another SQL statement is called a subquery.


https://docs.oracle.com/database/121/SQLRF/queries007.htm#SQLRF52357

A subquery in the FROM clause of a SELECT statement is also called an inline view


We can use either subquery or inline-view to name such queries.
The latter is more strict.

perhaps an example helps?

Duke Ganote, August 15, 2016 - 6:12 pm UTC

WITH
factored_subquery AS -- ANSI SQL-99: "Common Table Expression"
( SELECT 1 AS scalar_value
    FROM DUAL
)
SELECT ( SELECT *
    FROM factored_subquery ) as scalar_subquery
     , scalar_value
  FROM
( SELECT *
    FROM factored_subquery ) /* as */ inline_view;

SCALAR_SUBQUERY SCALAR_VALUE
--------------- ------------
              1            1


https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594885400346999596

Chris Saxon
August 16, 2016 - 1:13 am UTC

Nice input.

... and factored subqueries are not even logically equivalent to inline views

Sokrates, August 15, 2016 - 6:52 pm UTC

see:
with function f return sys_refcursor is
ret sys_refcursor;
begin
   open ret for select * from dual;
   return ret;
end f;
select count(*) from
   (select f() f from dual connect by level <= 10),
   (select f() f from dual connect by level <= 10)
/

  COUNT(*)
----------
       100


but

with function f return sys_refcursor is
ret sys_refcursor;
begin
   open ret for select * from dual;
   return ret;
end f;
iv as (select f() f from dual connect by level <= 10)
select count(*) from
   iv, iv
/
iv as (select f() f from dual connect by level <= 10)
              *
ERROR at line 7:
ORA-02345: cannot create a view with column based on CURSOR operator

Connor McDonald

Pravin Yadav, August 22, 2016 - 9:19 am UTC