 |
|
Advanced Oracle SQL: Nested Subqueries
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
Oracle SQL Programming" by Rampant TechPress. The following
is an excerpt from the book.
Again, from the department table, the department
that is different from all departments in EMP is returned. Also, it
does not matter which of the syntaxes one uses. This was not the
case in previous versions. Nowadays, statistics are used by Oracle
to choose the best plan, irrespective of query syntax.
1.
Scalar subquery
Scalar subqueries return, at most, one row. If
the subquery returns no row, the value NULL is passed to the main
query:
SELECT
(
SELECT
COUNT(*)
FROM
EMP
)+(
SELECT
COUNT(*)
FROM
DEPT
) as "rows in dept + rows in emp"
FROM
DUAL;
rows
in dept + rows in emp
--------------------------
18
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0)| 00:00:01
|
| 3 | SORT AGGREGATE | | 1 | |
|
| 4 | INDEX FULL SCAN| PK_DEPT | 4 | 1 (0)| 00:00:01
|
| 5 | FAST DUAL | | 1 | 2 (0)| 00:00:01
|
----------------------------------------------------------------------
A scalar subquery can be used in SQL
expressions. It is sometimes called a single-row subquery.
2.
Correlated subquery
Nested subqueries are correlated if they
reference a column of the main query. Scalar subqueries in the
SELECT clause may be related to a table in the FROM clause.
SELECT
EMP.ENAME,
(
SELECT
DEPT.DNAME
FROM
DEPT
WHERE
DEPT.DEPTNO=EMP.DEPTNO
) as DNAME
FROM
EMP;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 |
3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 |
1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | |
0 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 |
3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
2 -
access("DEPT"."DEPTNO"=:B1)
The department is retrieved for each row:
SELECT
E1.ENAME,
E1.JOB
FROM
EMP E1
WHERE
NOT EXISTS
(
SELECT
*
FROM
EMP E2
WHERE
E1.JOB = E2.JOB
AND
E1.EMPNO != E2.EMPNO
);
ENAME JOB
---------- ---------
KING PRESIDENT
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 7 (15)|
00:00:01 |
|* 1 | HASH JOIN ANTI | | 14 | 420 | 7 (15)|
00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 252 | 3 (0)|
00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 168 | 3 (0)|
00:00:01 |
---------------------------------------------------------------------------
1 -
access("E1"."JOB"="E2"."JOB")
filter("E1"."EMPNO"<>"E2"."EMPNO")
The main table has an alias E1 and is referenced
in the subquery.
