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 a

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, =, > 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.