A subquery is quite intuitive operation and it is easy to understand its meaning. In a common, top-level statement is called an outer query and a query nested within another SQL statement is called a subquery.
Nested Subquery
A subquery in the FROM clause of a SELECT statement is also called an inline view. Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins. You can nest up to 255 levels of subqueries in the a nested subquery. A nested subquery conceptually is evaluated once for each row processed by the parent statement.
Correlated Subquery
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one or more levels above the subquery or nested subquery.
Table alias
Table alias (t_alias) specified a correlation name (alias) followed by a period and the asterisk to select all columns from the object with that correlation name specified in the FROM clause of the same subquery. The object can be a table, view, materialized view, or subquery.
Basic Recommendations
If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.
Example
The example demonstrates three correlated subqueries. First SELECT statement runs without error, but returns a wrong result. The second results in an error. The third query uses table aliases, runs without error and returns the correct result.
SQL> select count(*) from EMPLOYEES where DEPARTMENT_ID in (select
DEPARTMENT_ID from DEPARTMENTS where EMPLOYEE_ID = DEPARTMENT_ID);
COUNT(*)
----------
0
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 7qqbgc6xfjnqc, child number 0
-------------------------------------
select count(*) from EMPLOYEES where DEPARTMENT_ID in (select
DEPARTMENT_ID from DEPARTMENTS where EMPLOYEE_ID = DEPARTMENT_ID)
Plan hash value: 2198345614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | VIEW | index$_join$_001 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX | 1 | 7 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"="DEPARTMENT_ID")
3 - access(ROWID=ROWID)
4 - filter("DEPARTMENT_ID" IS NOT NULL)
25 rows selected.
SQL> select count(*) from EMPLOYEES E where E.DEPARTMENT_ID in (select D.DEPARTMENT_ID from DEPARTMENTS D where D.EMPLOYEE_ID = E.DEPARTMENT_ID)
*
ERROR at line 1:
ORA-00904: "D"."EMPLOYEE_ID": invalid identifier
SQL> select count(*) from EMPLOYEES E where E.DEPARTMENT_ID in (select
D.DEPARTMENT_ID from DEPARTMENTS D where D.DEPARTMENT_ID =
E.DEPARTMENT_ID);
COUNT(*)
----------
106
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 7rsjy0hgc6hcv, child number 0
-------------------------------------
select count(*) from EMPLOYEES E where E.DEPARTMENT_ID in (select
D.DEPARTMENT_ID from DEPARTMENTS D where D.DEPARTMENT_ID =
E.DEPARTMENT_ID)
Plan hash value: 2265163291
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| EMP_DEPARTMENT_IX | 106 | 318 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
21 rows selected.
Execution the subquery alone reproduces a syntax error. But execution the same subquery nested beneath the parent query, reproduces no error, but it leads to incorrect whole query results.
The reason to require always use table aliases is obvious – there is a high probability to get wrong results in case of wrong column name usage when there is no table aliases in subqueries
IN & EXISTS conditions
The correlated subqueries are common as conditions in WHERE clause. An in_condition is a membership condition. It tests a value for membership in a list of values or subquery. An EXISTS condition tests for existence of rows in a subquery
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:953229842074
IN (vs) EXISTS and NOT IN (vs) NOT EXISTS
https://asktom.oracle.com/pls/apex/f?p=100:11:0::no::p11_question_id:442029737684
Try subqueries yourself through examples (documentation):
An easier way to figure this out is to try and run the subquery yourself. Subqueries: Databases for Developers – tutorial on Live SQL by Chris Saxon
https://livesql.oracle.com/apex/livesql/file/tutorial_GMLYIBY74FPBS888XO8F1R95I.html
Image by Hermann Schmider from Pixabay
Reviews and walkthroughs for the operation of high-performance systems 