Oracle SQL Sub Query Analysis

A sub query embedded in outer query with brackets.

For example:

Limitations:

• Oracle allows up to 255 levels sub queries in where clause.
• Oracle allows unlimited number of sub queries in from clause.

There are three broad divisions of sub query:

• Single-row sub queries
• Multiple-row sub queries
• Correlated sub queries

Single- and Multiple-Row Sub queries

The single-row sub query returns one row. A special case is the scalar sub query, which returns a single row with one column. 

On the other hand, Multiple-row sub queries return sets of rows. 

The flow of execution for both single-row and multiple-row sub queries:

1. Sub query is evaluated at once, before evaluating the outer query.

2. Then outer query will run.

3. Check each row of outer query with the result[s] of sub query for eligible the row in the output.

For Example:

we'll use EMP table in which following data exist:

select * from emp;

Now we will query the employees who gets salary less than average:

select * from emp where salary < (select avg(salary) from emp);

In the above query, the inner query produce the average salary. And the outer query checks each row salary of EMP table whether it is less than the inner query result.

The comparison operator for a single-row sub query:

Symbol	Meaning
=	equal
>	greater than
>=	greater than or equal
<	less than
<=	less than or equal
<>	not equal
!=	not equal

If your sub query returns multiple rows and you are using above operators then following error occurred: 

ORA-01427: single-row subquery returns more than one row

select * from emp where salary < (select avg(salary) from emp where empid in (1,10));

The comparison operator for a multiple-row sub query:

Symbol	Meaning
IN	equal to any member in a list
NOT IN	not equal to any member in a list
ANY	returns rows that match any value on a list
ALL	returns rows that match all the values in a list

For example:

select * from emp where salary = any (select salary from emp WHERE salary>500 );

In the above query, the inner query produce the multiple salary results which are greater than 500. And after that outer query checks each row salary of EMP table whether exists in the list of sub query results.



Correlated Sub queries

If a sub query references columns in the parent query, then its result will be dependent on the parent query. This makes it impossible to evaluate the sub query before evaluating the parent query. 

We analyze correlated sub query on EMP table having following data:

	EMPID	SALARY
1	1	200
2	10	150
3	15	10000
4	6	11100
5	4	12000
6	5	5000
7	20	2000
8	30	200
9	40	200

Now we concentrate on the below complex sub query:

select * from
emp a
where 2 = (select count(distinct(b.salary)) from emp b where b.salary<=a.salary);

Can you imagine?  The logic behind the sub query is Each row scans whole table check how many distinct values are lower than or equal this value and its is 2 or not. That means it'll produce rows which have 2nd lowest salary.

Now we analyze this. The correlated sub query will be executed as follows:

1. Start at the first row of EMP table of outer query.
2. Read the current row salary of EMP table.
3. Run the sub query with the salary from step 2.
4. Compare the result of step 3 with the value 2.
5. Advances to the next row of EMP table.
6. Repeat from Step 2.

Now we simulate the data of EMP table for the the above 

Outer Query		Cond.	Sub query Result	Sub query Cond.	Sub Query
A					B
Outer Query    EMPID SALARY 1 1 200 2 10 150 3 15 10000 4 6 11100 5 4 12000 6 5 5000 7 20 2000 8 30 200 9 40 200		2 =	Count ( distinct(salary) )	B.SALARY <= A.SALARY	Inner Query    EMPID SALARY 1 1 200 2 10 150 3 15 10000 4 6 11100 5 4 12000 6 5 5000 7 20 2000 8 30 200 9 40 200
Rows	Current Row Salary	Cond.	Sub query Result			Eligible?
1	200	2=	2	[10000,11100,12000,150,200,2000,5000] <= 200		Y
2	150	2=	1	[10000,11100,12000,150,200,2000,5000] <=150		N
3	10000	2=	5	[10000,11100,12000,150,200,2000,5000] <= 10000		N
4	11100	2=	6	[10000,11100,12000,150,200,2000,5000] <= 11100		N
5	12000	2=	7	[10000,11100,12000,150,200,2000,5000] <= 12000		N
6	5000	2=	4	[10000,11100,12000,150,200,2000,5000] <= 5000		N
7	2000	2=	3	[10000,11100,12000,150,200,2000,5000] <= 2000		N
8	2000	2=	3	[10000,11100,12000,150,200,2000,5000] <= 2000		N
9	200	2=	2	[10000,11100,12000,150,200,2000,5000] <=200		Y