Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Saturday, 2 April 2016

SQL Subqueries

A form of an SQL statement that appears inside another SQL statement, also termed as Nested Query
The statement containing a sub-query is called a parent statement
The parent statement uses the rows returned by the sub-query


Single row subqueries
Multi row subqueries
Multiple subqueries
Correlated subqueries


In single row subquery, it will return one value.

    SQL> select * from emp where sal > (select sal from emp where empno = 7566);

     EMPNO   ENAME      JOB        MGR    HIREDATE    SAL   COMM     DEPTNO
       ---------- ---------- --------- ---------- ------------  ------- ---------- ----------
      7788         SCOTT   ANALYST    7566   19-APR-87   3000            20
      7839         KING     PRESIDENT            17-NOV-81  5000             10
      7902         FORD    ANALYST     7566   03-DEC-81   3000            20


In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.

     SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and

     EMPNO    ENAME      JOB      MGR     HIREDATE   SAL   COMM     DEPTNO
    ---------- ---------- --------- ---------- -----------   -------- ---------- ----------
      7566         JONES   MANAGER   7839 02-APR-81   2975                   20
      7788         SCOTT   ANALYST    7566 19-APR-87    3000                   20
      7839         KING     PRESIDENT          17-NOV-81   5000                   10
      7902         FORD    ANALYST     7566 03-DEC-81    3000                   20

       SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and
     EMPNO      ENAME    JOB       MGR     HIREDATE     SAL    COMM  DEPTNO
     ---------- ---------- --------- ---------- -------------  ------ ---------- ----------
      7839         KING     PRESIDENT            17-NOV-81  5000                    10


There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.

     SQL> select * from emp where sal = (select max(sal) from emp where sal < (select
              max(sal) from emp));

     EMPNO      ENAME   JOB      MGR       HIREDATE   SAL   COMM     DEPTNO
     ---------- ---------- --------- ---------- ------------  ------- ---------- ----------
      7788         SCOTT   ANALYST  7566    19-APR-87   3000                    20
     7902          FORD    ANALYST   7566    03-DEC-81   3000                    20


A Subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.
     SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno);

Share this article :


  1. Hi Dayakar,

    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Subqueries! I agree with you on lot of points!

    I have procedure which have cursor and this cursor is loading the data into some collection variables.
    When I am executing this procedure and when it is loading the cursor values into collection variables.

    I got the following exception:

    --FAILED-ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: '/ora02/oradata/pujty/TEMP_01.dbf.

    Even I am unable to see that file at that location also.
    Could you please help me to understand this.

    Appreciate your effort for making such useful blogs and helping the community.


  2. Salaam,

    So bloody thorough! Ah! So happy and blissed out! I feel redeemed by reading out #topic. Keep up the good work!

    I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions


    1:Low selectivity indexes.
    2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
    3:All these normal(Balances -Tree )indexes.

    They are asking the App Team to review these and to drop those indexes.
    But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
    After applying the HINT of those indexes also the cost of query was decreasing.

    Here my confusion was,if we really drop those indexes, will it increase the performance?
    Do we need to convert those as BITMAP as they have low cardinality ?

    Please help me to understand the concept behind this .

    Thank you very much and will look for more postings from you.

    Thanks and Regards