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

Pages

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

TYPES

Single row subqueries
Multi row subqueries
Multiple subqueries
Correlated subqueries

SINGLE ROW SUBQUERIES

In single row subquery, it will return one value.

Ex:
    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

MULTI ROW SUBQUERIES

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.



Ex:
     SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and
             4000);

     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
              4000);
 
     EMPNO      ENAME    JOB       MGR     HIREDATE     SAL    COMM  DEPTNO
     ---------- ---------- --------- ---------- -------------  ------ ---------- ----------
      7839         KING     PRESIDENT            17-NOV-81  5000                    10

MULTIPLE SUBQUERIES

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

Ex:
     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

CORRELATED SUBQUERIES

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.
Ex:
     SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno);
    DEPTNO
    ----------
        20

        30
Share this article :

0 comments:

Post a Comment