 |
|
Advanced Oracle SQL: Subquery Inline View
Oracle Tips by Laurent Schneider
|
Laurent
Schneider is considered one of the top Oracle SQL experts, 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 query returns only those with a salary less than 2500.
The subquery could be saved as a view, providing the
necessary privileges are granted. In fact, a subquery in the FROM clause is
called an inline view and might look like the following where the view is
created before the selection. 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 a subquery, 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 is compared
with multiple values of the subquery. IN checks if the left value is in the
subquery. NOT IN checks if the left value is not in the subquery. With IN and
NOT IN, it is possible to have an expression list on the left side. The number
of columns of the subquery must match the number of expressions in the left
expression list. EXISTS has no left operand and checks if the subquery returns
at least one row. The number of columns is irrelevant, so star (*) is just 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 all departments in EMP is returned.
A subquery in the WHERE clause is called a nested
subquery. The join between the two tables is an antijoin.
It is important to note the NOT NULL condition in NOT IN and
!=ALL. If one department 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.
and include the URL for the page.
