Advanced Oracle SQL: Subquery Inline View

Advanced Oracle SQL: Subquery Inline View Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQLexperts, and he is the author of the book "Advanced SQL Programming"by Rampant TechPress.  The following is an excerpt from the book.

In the FROM clause below, a subquery acts as a table:

SELECT
   ENAME
FROM
(
   SELECT
      EMPNO,
      ENAME,
      SAL
   FROM
      EMP
   WHERE
      DEPTNO=10
)
WHERE
   SAL<2500;

ENAME
----------
CLARK
MILLER

--------------------------------------------------------------------------
| Id  | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    13 |    3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1|    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

   1 - filter("DEPTNO"=10 AND "SAL"<2500)

The subquery returns all employees of department 10, and the main queryreturns only those with a salary less than 2500.

The subquery could be saved as a view, providing the necessary privilegesare granted. In fact, a subquery in the FROM clause is called an inline viewand might look like the following where the view is created before theselection.  The selection starts with the WITH statement:

CREATE VIEW
   DEPT10
AS
SELECT
   EMPNO,
   ENAME,
   SAL
FROM
   EMP
WHERE
   DEPTNO=10
/
SELECT
   ENAME
FROM
   DEPT10
WHERE
   SAL<2500;
WITH
   DEPT10
AS
(
   SELECT
      EMPNO,
      ENAME,
      SAL
   FROM
      EMP
   WHERE
      DEPTNO=10
)
SELECT
   ENAME
FROM
   DEPT10
WHERE
   SAL<2500; 

Subquery factoring was introduced in Oracle 9i. Instead of using asubquery, the two conditions, salary less than 2500 and department equal 10,could be combined by an AND logical operator.

Nested subquery

Subqueries can be used in logical statements like =ALL, >SOME, <ANY,IN, EXIST.  SOME and ANY are equivalent. By using an operator like <,<=, =, !=, >=, > followed by SOME, ANY or ALL, the left operand iscompared with multiple values of the subquery. IN checks if the left value isin the subquery. NOT IN checks if the left value is not in the subquery. WithIN and NOT IN, it is possible to have an expression list on the left side. Thenumber of columns of the subquery must match the number of expressions in theleft expression list. EXISTS has no left operand and checks if the subqueryreturns at least one row. The number of columns is irrelevant, so star (*) isjust fine. NOT EXISTS is true when the subquery returns no rows.

The three queries that follow create the same result and the same plan:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   DEPTNO!=ALL
   (
      SELECT
         EMP.DEPTNO
      FROM
         EMP
      WHERE
         EMP.DEPTNO IS NOT NULL
   );
 
    DEPTNO
----------
        40

------------------------------------------------------------------------------
| Id  | Operation          |Name    | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     3|    18 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI   |         |     3|    18 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |    4 |    12 |    1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP    |    14 |    42 |    3   (0)| 00:00:01 |
------------------------------------------------------------------------------

   1 - access("DEPTNO"="EMP"."DEPTNO")
   3 - filter("EMP"."DEPTNO" IS NOT NULL)

Or:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   DEPTNO NOT IN
   (
      SELECT
         EMP.DEPTNO
      FROM
         EMP
      WHERE
         EMP.DEPTNO IS NOT NULL
   );
 
    DEPTNO
----------
        40

------------------------------------------------------------------------------
| Id  | Operation          |Name    | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     3 |   18 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI   |         |     3|    18 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT|     4 |    12 |    1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP    |    14 |    42 |    3   (0)| 00:00:01 |
------------------------------------------------------------------------------
   1 - access("DEPTNO"="EMP"."DEPTNO")
   3 - filter("EMP"."DEPTNO" IS NOT NULL)

Or:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   NOT EXISTS
   (
      SELECT
         *
      FROM
         EMP
      WHERE
         EMP.DEPTNO=DEPT.DEPTNO
   );
 

    DEPTNO
----------
        40

------------------------------------------------------------------------------
| Id  | Operation          |Name    | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     3|    18 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI   |         |     3|    18 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT|     4 |    12 |     1  (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP    |    14 |    42 |    3   (0)| 00:00:01 |
------------------------------------------------------------------------------

   1 -access("EMP"."DEPTNO"="DEPT"."DEPTNO")

From the department table, the department that is different from alldepartments in EMP is returned.

A subquery in the WHERE clause is called a nested subquery.  The joinbetween the two tables is an antijoin.

It is important to note the NOT NULL condition in NOT IN and !=ALL. If onedepartment is null in EMP, it should not exclude department 40:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   DEPTNO=SOME
   (
      SELECT
         EMP.DEPTNO
      FROM
         EMP
   );

    DEPTNO
----------
        10
        20
        30

-------------------------------------------------------------------------------
| Id  |Operation           |Name    | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     3 |    18 |    4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |     3|    18 |     4  (25)| 00:00:01 |
|   2 |   SORT UNIQUE      |         |    14|    42 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL|EMP     |    14 |    42|     3   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN | PK_DEPT |    1 |     3 |     0   (0)|00:00:01 |
-------------------------------------------------------------------------------

   4 - access("DEPTNO"="EMP"."DEPTNO")

Or:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   DEPTNO IN
   (
      SELECT
         EMP.DEPTNO
      FROM
         EMP
   ); 

    DEPTNO
----------
        10
        20
        30

-------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     3|    18 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |     3|    18 |     4  (25)| 00:00:01 |
|   2 |   SORT UNIQUE      |         |    14|    42 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL|EMP     |    14 |    42|     3   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN | PK_DEPT |    1 |     3 |     0   (0)|00:00:01 |
-------------------------------------------------------------------------------

   4 -access("DEPTNO"="EMP"."DEPTNO")

Or:

SELECT
   DEPTNO
FROM
   DEPT
WHERE
   EXISTS
   (
      SELECT
         *
      FROM
         EMP
      WHERE
         EMP.DEPTNO=DEPT.DEPTNO
   );

    DEPTNO
----------
        10
        20
        30

-------------------------------------------------------------------------------
| Id  | Operation          | Name    |Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     3|    18 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |     3|    18 |     4  (25)| 00:00:01 |
|   2 |   SORT UNIQUE      |         |    14|    42 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL|EMP     |    14 |    42|     3   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN | PK_DEPT |    1 |     3 |     0   (0)|00:00:01 |
-------------------------------------------------------------------------------

   4 -access("EMP"."DEPTNO"="DEPT"."DEPTNO")

This type of join is called a semijoin.