Oracle database tips logo
Article categories | Blog and News Page | SQL | SQL*Plus | DBA Scripts | Standby dbs | Database Links | Techno webcasts | Oracle Magazine | Free IT Magazines | Oracle Database Hosting Companies | Site Search |
RSS
XML RSS
What is this?
AddThis Feed Button

Social Bookmarking


Free Linux Manuals !


A Newbie's Getting Started Guide to Linux



The GNU/Linux Advanced Administration
-- FREE --
IT Magazine
Subscriptions

Oracle Magazine Oracle Magazine Contains technology strategy articles, sample code, tips, Oracle and partner news, how-to articles for developers and DBAs

WebSite Magazine WebSite Magazine Practical advice, helpful tools and insights for website owners

Dr Dobb's Journal Dr Dobb's Journal enables coders to write the most efficient programs and help in daily programming quandaries

DM Review DM Review is recognized as the premier business intelligence, analytics and data warehousing publication
Various other Free IT magazine subscriptions
NoAdware Free Trial

NoAdware Remove
harmful
adware,
spyware,
trojans,
dialers
and worms!
- Featured ebook -

Database Normalization
by Alf Pedersen

Database Normalization ebook Understand and master how to normalize a database using methods richly documented with graphical ERD and server diagram examples

Oracle Subquery
Examples and syntax explanation



Always test these 'oracle subquery' commands on a test server
before implementing them on a production server.

Contents

Definition

A subquery is basically a select clause which is used instead of another statement.

For example : Have a look at the following 2 tables : DEPT and EMP



SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL> select empno,ename,job,sal,deptno from emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10

14 rows selected.



Simple Oracle subquery

A simple subquery is evaluated once for each table.

You would like to select all employees whose department is located in Chicago.

A join would be a better solution for this select,
but for the purposes of illustration we will use a subquery.



SQL> select empno,ename,job,sal,deptno from emp where
     deptno in (select deptno from dept 
	 where loc = 'CHICAGO');

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK            950         30
      7844 TURNER     SALESMAN        1500         30
      7698 BLAKE      MANAGER         2850         30
      7654 MARTIN     SALESMAN        1250         30
      7521 WARD       SALESMAN        1250         30
      7499 ALLEN      SALESMAN        1600         30

6 rows selected.




You can use a subquery in the place of a table name in the 'from' clause.



SQL> Select ename, deptno from
     (select ename, deptno from emp 
	 where job = 'CLERK' )
     where
     deptno > 20;

ENAME          DEPTNO
---------- ----------
JAMES              30



You can even replace a column name with a subquery:


SQL> select ename, 
            (select max(sal) from emp) "maxsal" , 
            sal, 
            ((select max(sal) from emp ) - sal ) 
			"difference"  from emp;

ENAME          maxsal        SAL difference
---------- ---------- ---------- ----------
SMITH            5000        800       4200
ALLEN            5000       1600       3400
WARD             5000       1250       3750
JONES            5000       2975       2025
MARTIN           5000       1250       3750
BLAKE            5000       2850       2150
CLARK            5000       2450       2550
SCOTT            5000       3000       2000
KING             5000       5000          0
TURNER           5000       1500       3500
ADAMS            5000       1100       3900
JAMES            5000        950       4050
FORD             5000       3000       2000
MILLER           5000       1300       3700

14 rows selected.





Correlated Oracle subquery

A correlated Oracle subquery is evaluated once FOR EACH ROW as opposed to a normal subquery which is evaluated only once for each table.

You can reference the outer query inside the correlated subquery using an alias which makes it so handy to use.

Let's select all employees whose salary is less than the average of all the employees' salaries in the same department.


SQL> select ename ,sal ,deptno from emp a where
     a.sal < (select avg(sal) from emp b 
	           where a.deptno = b.deptno) 
     order by deptno;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
MILLER           1300         10
SMITH             800         20
ADAMS            1100         20
WARD             1250         30
MARTIN           1250         30
TURNER           1500         30
JAMES             950         30

8 rows selected.




Using a correlated subquery in an update

Let's give these people (whose salary is less than their department's average) a raise.



SQL> select ename, sal, deptno  from emp 
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
KING             5000         10
MILLER           1300         10
ADAMS            1100         20
FORD             3000         20
JONES            2975         20
SCOTT            3000         20
SMITH             800         20
ALLEN            1600         30
BLAKE            2850         30
JAMES             950         30
MARTIN           1250         30
TURNER           1500         30
WARD             1250         30

14 rows selected.



SQL>  UPDATE emp a 
      set sal = (select avg(sal) 
                from emp b 
	        where 
	        a.deptno = b.deptno)
      where sal < 
	   (select avg(sal) from emp c 
	   where a.deptno = c.deptno);

8 rows updated.

SQL> select ename, sal, deptno  from emp 
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK         2916.67         10
KING             5000         10
MILLER        2916.67         10
ADAMS            2175         20
FORD             3000         20
JONES            2975         20
SCOTT            3000         20
SMITH            2175         20
ALLEN            1600         30
BLAKE            2850         30
JAMES         1566.67         30
MARTIN        1566.67         30
TURNER        1566.67         30
WARD          1566.67         30

14 rows selected.

SQL> commit;

Commit complete.




Using a correlated subquery in a delete

Let's delete the highest earning employees in each department.



SQL>  delete from emp a where
      a.sal = (select max(sal) from emp b 
	  where a.deptno = b.deptno);

4 rows deleted.

SQL> select ename, sal, deptno  from emp 
     order by deptno, ename;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK         2916.67         10
MILLER        2916.67         10
ADAMS            2175         20
JONES            2975         20
SMITH            2175         20
ALLEN            1600         30
JAMES         1566.67         30
MARTIN        1566.67         30
TURNER        1566.67         30
WARD          1566.67         30

10 rows selected.

SQL> commit;

Commit complete.




Lastly: do not forget to commit to make your changes permanent when using the oracle subquery statement.


Recommended reading:


  • Oracle Online Documentation: SQL Reference, Using Subqueries


  • Return from 'oracle subquery' back to 'oracle sql tutorials'