//
you're reading...
SQL

A Short Guide to Oracle SQL [VIII] – Subqueries

Subqueries

A subquery is a nested query – a query within a query. A subquery’s output are used to determine the results of the outer query (the query that contains the subquery).

    • Subqueries can be used in SELECT, FROM, WHERE and HAVING clause.

Subquery has the same form as SELECT statement except it is enclosed in parenthesis.

Single-row Subquery

A single row subquery returns a single row of result.

hr@O11R2> SELECT employee_id, first_name||' '||last_name Name, job_id, salary
     FROM employees
     WHERE salary = (SELECT MIN(salary)
                     FROM employees);

EMPLOYEE_ID NAME                                           JOB_ID     SALARY
----------- ---------------------------------------------- ---------- --------
        132 TJ Olson                                       ST_CLERK       2100

hr@O11R2> SELECT employee_id, first_name||' '||last_name Name, job_id, salary, hire_date
     FROM employees
     WHERE salary > (SELECT AVG(salary)
                     FROM employees)
     AND commission_pct IS NULL;

EMPLOYEE_ID NAME                                           JOB_ID         SALARY HIRE_DATE
----------- ---------------------------------------------- ---------- ---------- ---------
        201 Michael Hartstein                              MK_MAN          13000 17-FEB-04
        203 Susan Mavris                                   HR_REP           6500 07-JUN-02
        204 Hermann Baer                                   PR_REP          10000 07-JUN-02
        205 Shelley Higgins                                AC_MGR          12008 07-JUN-02
        206 William Gietz                                  AC_ACCOUNT       8300 07-JUN-02
        100 Steven King                                    AD_PRES         24000 17-JUN-03
        101 Neena Kochhar                                  AD_VP           17000 21-SEP-05
        102 Lex De Haan                                    AD_VP           17000 13-JAN-01
        103 Alexander Hunold                               IT_PROG          9000 03-JAN-06
        108 Nancy Greenberg                                FI_MGR          12008 17-AUG-02
        109 Daniel Faviet                                  FI_ACCOUNT       9000 16-AUG-02
        110 John Chen                                      FI_ACCOUNT       8200 28-SEP-05
        111 Ismael Sciarra                                 FI_ACCOUNT       7700 30-SEP-05
        112 Jose Manuel Urman                              FI_ACCOUNT       7800 07-MAR-06
        113 Luis Popp                                      FI_ACCOUNT       6900 07-DEC-07
        114 Den Raphaely                                   PU_MAN          11000 07-DEC-02
        120 Matthew Weiss                                  ST_MAN           8000 18-JUL-04
        121 Adam Fripp                                     ST_MAN           8200 10-APR-05
        122 Payam Kaufling                                 ST_MAN           7900 01-MAY-03
        123 Shanta Vollman                                 ST_MAN           6500 10-OCT-05

20 rows selected.

-- job with lowest average salary
hr@O11R2> SELECT job_id, AVG(salary)
     FROM employees
     GROUP BY job_id
     HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                           FROM employees
                           GROUP BY job_id);

JOB_ID     AVG(SALARY)
---------- -----------
PU_CLERK          2780

It is possible to a single row subquery to return exactly one column with single value. This kind of subquery called ‘Scalar Subquery‘.

hr@O11R> SELECT department_id, department_name,
                 (SELECT MAX(salary)
                 FROM employees
                 WHERE employees.department_id=departments.department_id) "Max Salary"
     FROM departments;

DEPARTMENT_ID DEPARTMENT_NAME                Max Salary
------------- ------------------------------ ----------
           10 Administration                       4400
           20 Marketing                           13000
           30 Purchasing                          11000
           40 Human Resources                      6500
           50 Shipping                             8200
           60 IT                                   9000
           70 Public Relations                    10000
           80 Sales                               14000
           90 Executive                           24000
          100 Finance                             12008
          110 Accounting                          12008

11 rows selected.

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

tarzan@O11R2> SELECT prj.cl_proj_stu_id "Student ID",  s_first_name||' '||s_last_name "Name",
              prj.cl_project_title "Project", prj.cl_project_marks "Marks"
     FROM student, (SELECT cl_proj_stu_id, cl_project_marks, cl_project_title
                    FROM class_project
                    WHERE cl_project_marks = (SELECT MAX(cl_project_marks)
                                              FROM class_project)
                   ) prj
     WHERE prj.cl_proj_stu_id=student.studentid;

Student ID Name             Project                                      Marks
---------- ---------------- -------------------------------------------  ----------
     90166 Steve Kapoor     A Library of Historic Photographs                   182

You can nest subqueries to a depth of 255. But there is no limit when a subquery is nested in the FROM clause.

You can also place a subquery in having clause.

scott@O11R2> SELECT  job_id, AVG(salary)
             FROM hr.employees
             GROUP BY  job_id
             HAVING   min(salary) = ( SELECT MAX(MIN(salary))
                                    FROM hr.employees
                                    GROUP BY department_id );

JOB_ID     AVG(SALARY)
---------- -----------
AD_VP            17000

Subquery can be used with DML statements.

hr@O11R2> UPDATE employees
          SET salary = (SELECT salary 
                        FROM employees
                        WHERE employee_id=103)
          WHERE employee_id=141;

1 row updated.

Multiple-row Subquery

Multiple-row subquery returns one or more rows of results to the parent query. Multiple-row subquery uses multiple-row operators. Use of single row operator results in error ORA-01427.

SQL> SELECT description, cost, prerequisite 
    FROM course 
    WHERE course_no = (SELECT course_no 
                      FROM section 
                      WHERE location = 'M311');

   WHERE course_no = (SELECT course_no 
                      *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> SELECT description, cost, prerequisite 
    FROM course 
    WHERE course_no IN (SELECT course_no 
                       FROM section 
                       WHERE location = 'M311');

DESCRIPTION                                              COST PREREQUISITE
-------------------------------------------------- ---------- ------------
Intro to Programming                                     1195          140
Intermediate Java Programming                            1195          120
Database System Principles                               1195           25
IN/NOT IN

IN operator is used to evaluate equality to any member of the list return by the subquery.

scott@O11R2> SELECT empno, ename, deptno, job, sal
     FROM emp
     WHERE sal IN (SELECT MIN(sal)
                   FROM emp
                   GROUP BY deptno)
     ORDER BY 3;

     EMPNO ENAME          DEPTNO JOB              SAL
---------- ---------- ---------- --------- ----------
      7934 MILLER             10 CLERK           1300
      7369 SMITH              20 CLERK            800
      7900 JAMES              30 CLERK            950

NOT IN operator performs logical opposite of IN operator. It gives the outer query, rows that do not match with the values provided by the inner query.

scott@O11R2> SELECT empno, ename, deptno, job, sal
          FROM emp
          WHERE deptno NOT IN (SELECT deptno
                              FROM dept
                              WHERE loc = 'DALLAS');

     EMPNO ENAME          DEPTNO JOB              SAL
---------- ---------- ---------- --------- ----------
      7934 MILLER             10 CLERK           1300
      7839 KING               10 PRESIDENT       5000
      7782 CLARK              10 MANAGER         2450
      7900 JAMES              30 CLERK            950
      7844 TURNER             30 SALESMAN        1500
      7698 BLAKE              30 MANAGER         2850
      7654 MARTIN             30 SALESMAN        1250
      7521 WARD               30 SALESMAN        1250
      7499 ALLEN              30 SALESMAN        1600
ANY

ANY operator is used with other comparison operator (<, >, >=, <= etc) and returns TRUE if the condition is TRUE for at least one of the value return by the subquery.

-- When ANY is used with equal to (=), it basically work like IN operator. Since we know the minimum
-- salary in each department (from the query above)
-- we can write ANY part to WHERE sal IN (800, 950, 1300).

scott@O11R2> SELECT empno, ename, deptno, job, sal
          FROM emp
          WHERE sal = ANY (SELECT MIN(sal)
                        FROM emp
                        GROUP BY deptno);      

     EMPNO ENAME          DEPTNO JOB              SAL
---------- ---------- ---------- --------- ----------
      7369 SMITH              20 CLERK            800
      7900 JAMES              30 CLERK            950
      7934 MILLER             10 CLERK           1300

-- When used with greater than (>), it gives the all rows with value (in this case SAL) greater than
-- the lowest value (which is 800) returned by the subquery. 
scott@O11R2> SELECT empno, ename, deptno, job, sal
          FROM emp
          WHERE sal > ANY (SELECT MIN(sal)
                        FROM emp
                        GROUP BY deptno);

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

13 rows selected.

-- When used with less than (<), ANY gives the all rows with value (in this case SAL) less than
-- the highest value (which is 1300 in this case). 
scott@O11R2> SELECT empno, ename, deptno, job, sal
          FROM emp
          WHERE sal < ANY (SELECT MIN(sal)
                        FROM emp
                        GROUP BY deptno);
  
     EMPNO ENAME          DEPTNO JOB              SAL
---------- ---------- ---------- --------- ----------
      7369 SMITH              20 CLERK            800
      7521 WARD               30 SALESMAN        1250
      7654 MARTIN             30 SALESMAN        1250
      7876 ADAMS              20 CLERK           1100
      7900 JAMES              30 CLERK            950
ALL

ALL operator is used with other comparison operator (<, >, >=, <= etc) and returns TRUE if the condition is TRUE for all the value return by the subquery.

-- ALL when used with less than (<), ALL returns the rows with a value (in this case SAL) lower than
-- the lowest value (which is 800) returned by subquery. This query returns no rows. 
scott@O11R2> SELECT empno, ename, deptno, job, sal
               FROM emp
               WHERE sal < ALL (SELECT MIN(sal)
                             FROM emp
                             GROUP BY deptno);

no rows selected.

-- ALL when used with greater than (>), it returns the rows with a value (in this case SAL) greater than
-- the highest value (which is 1300) returned by subquery. 
scott@O11R2> SELECT empno, ename, deptno, job, sal
               FROM emp
               WHERE sal > ALL (SELECT MIN(sal)
                             FROM emp
                             GROUP BY deptno);

     EMPNO ENAME          DEPTNO JOB              SAL
---------- ---------- ---------- --------- ----------
      7499 ALLEN              30 SALESMAN        1600
      7566 JONES              20 MANAGER         2975
      7698 BLAKE              30 MANAGER         2850
      7782 CLARK              10 MANAGER         2450
      7788 SCOTT              20 ANALYST         3000
      7839 KING               10 PRESIDENT       5000
      7844 TURNER             30 SALESMAN        1500
      7902 FORD               20 ANALYST         3000

8 rows selected.
EXIST/NOT EXIST

EXISTS etermines if any rows exist in the result of a subquery. EXISTS returns true if subquery returns at least one row and otherwise it returns false. Basically used with correlated subqueries.

scott@O11R2> SELECT deptno, dname
       FROM dept 
       WHERE EXISTS (SELECT e.deptno
                      FROM emp e
                      WHERE dept.deptno = e.deptno);

    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING

NOT EXIST is just the opposite of EXIST.

scott@O11R2> SELECT deptno, dname
       FROM dept 
       WHERE NOT EXISTS (SELECT e.deptno
                          FROM emp e
                         WHERE dept.deptno = e.deptno);

    DEPTNO DNAME
---------- --------------
        40 OPERATIONS

Multiple-column Subquery

A Multiple-column subquery compares more than one column between outer query and inner query.

scott@O11R2> SELECT employee_id, first_name||' '||last_name "Name", job_id, salary 
           FROM hr.EMPloyees
           WHERE (job_id, salary) IN (SELECT job_id, salary 
                                    FROM hr.employees 
                                    WHERE employee_id=155);

EMPLOYEE_ID Name                      JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        155 Oliver Tuvault            SA_REP           7000
        161 Sarath Sewall             SA_REP           7000
        178 Kimberely Grant           SA_REP           7000

Correlated Subqueries

A correlated subquery is a subquery that references the one or more columns of the outer query. Correlated subqueries are more system resource-intensive than regular or simple subqueries, because the
correlated subquery is executed once for each row in the outer query. Regular subqueries are executed
only once.

-- The following query gives the listing of highest paid employees of each dept.
scott@O11R2> SELECT ename, sal, deptno 
            FROM emp t1 
            WHERE sal = (SELECT MAX (sal) 
                         FROM emp 
                         WHERE deptno=t1.deptno) 
            ORDER BY deptno;

ENAME             SAL     DEPTNO
---------- ---------- ----------
KING             5000         10
FORD             3000         20
SCOTT            3000         20
BLAKE            2850         30

-- It start with the first row of outer query statement and executing subquery for that particular
-- row. Then it compares (salary) the result and after that advance to the next rows.
Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: