Monday 6 August 2012

What are Sub Queries in ORACLE

Nesting of queries, i.e., a query which is inside another query.  Here the outer query is called the parent query, which gets a result from the inner query.  So the inner query gets executed first and based on its result the outer query gets executed. 
Note1:
  • Do no add an order by clause to a sub query.
  • Use single row operator with single row sub query.
  • Use multiple row operators with multiple-row sub query.
  • The single row sub query operators are =, >, <, <=, >=, <>, ^=, !=
  • The multiple row operators are IN, ANY, ALL
Note: 2
  • ‘=any’ means IN and
  • ‘!=all’ means NOT IN
Note: 3
  • <any means less than maximum
  • >any means greater than minimum
  • <all means less than minimum
  • >all means greater than maximum

SUB-QUERIES:SINGLE ROW SUB QUERY

SQL> select * from emp where sal>(select sal from emp where ename='SCOTT');

    EMPNO ENAME      JOB          HIREDATE        SAL        DEPTNO
--------- ---------- ---------    --------- --------- --- ---------
     7839 KING       PRESIDENT     17-NOV-81      5000         10

SQL >select * from emp where sal>=(select sal from emp where ename='SCOTT');

     EMPNO ENAME      JOB         HIREDATE        SAL         DEPTNO
--------- ---------- ---------  --------- ---------        ---------
     7788 SCOTT      ANALYST     09-DEC-82      3000          20
     7839 KING       PRESIDENT   17-NOV-81      5000          10
     7902 FORD       ANALYST     03-DEC-81      3000          20

SQL >select ename,job from emp where job=(select job from emp where empno=7369);

ENAME      JOB
---------- ---------
SMITH      CLERK
ADAMS      CLERK
JAMES      CLERK
MILLER     CLERK

SQL > select ename,job from emp where job=(select job from emp where empno=7369)and sal>(select sal from emp where empno=7876);

ENAME      JOB
---------- ---------
MILLER     CLERK
 
SQL >select * from emp where job='CLERK';

    EMPNO  ENAME      JOB       HIREDATE        SAL         DEPTNO
 --------- - --------- ---------  --------- ---------       --------------------
     7369       SMITH      CLERK       17-DEC-80       800        20
     7876       ADAMS     CLERK       12-JAN-83      1100      20
     7900       JAMES      CLERK       03-DEC-81       950        30
     7934       MILLER     CLERK       23-JAN-82      1300      10

SUB-QUERIES: MULTIPLE ROW SUB QUERY

SQL >select empno,ename,sal from emp where sal=any(select sal from emp,salgrade
 where emp.sal between salgrade.losal and salgrade.hisal);

    EMPNO ENAME            SAL
--------- ---------- ---------
     7369 SMITH            800
     7900 JAMES            950
     7876 ADAMS           1100
     7521 WARD            1250
     7654 MARTIN          1250
     7934 MILLER          1300
     7844 TURNER          1500
     7499 ALLEN           1600
     7782 CLARK           2450
     7698 BLAKE           2850
     7566 JONES           2975
     7788 SCOTT           3000
     7902 FORD            3000
     7839 KING            5000
14 rows selected.

SQL >select empno,ename,sal from emp where sal !=all (select sal from emp,salgrade    where emp.sal between salgrade.losal and salgrade.hisal);

no rows selected

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis