When we write a query inside another query, the inner query is called subquery.
Outer query is called parent query.
Subquery is executed first and the parent query will be executed by using the result
of the sub query.
Sub queries are used to get the results based on unknown values.
Types of Sub queries:
Single row subquery
Muiltiple row subquery
Multiple column subquery
Co-related subquery
Scalar subquery
Inline view
Single row subquery
When subquery returns one row ( one value ), it is called single-row subquery.
Ex:
I want to display all the employees who are having sal greater than ALLEN sal.
Step 1: we need to find ALLEN sal
Query to get ALLEN sal
Select sal from emp
Where ename=ALLEN ;
Output: 1600
Step 2:
Now, we want all the rows who are having sal > 1600
Query:
Select * from emp
Where sal > (Select sal from emp
Where ename=ALLEN );
Subquery is highlighted in blue color.
Always subquery should be used in parenthesis.
As we know, subquery is executed first , it returns 1600.
And then parent query will display all the rows who are having sal > ALLEN SAL
Ex 2:
I want to display the rows who are having sal > ALLEN sal and job same as JONES job.
Select * from emp
Where sal > ( select sal from emp
Where ename=ALLEN )
And job = ( select job from emp
Where ename=JONES);
One parent query can have any multiple subqueries.
Ex 3:
Write a query to display details of an employee who is having highest sal.
Step 1: Find the highest sal.
Select max(sal) from emp; -- output 5000
Step 2:
Select * from emp
Where sal = ( select max(sal) from emp );
In all the above examples, subquery is returning only one row ( one value ).
Hence they are called single-row subquery.
2) Multiple-row subquery
When subquery returns more than one row ( more than one value ),
They are called multiple-row subquery.
Ex:
Select * from emp
Where sal > ( select sal from emp
Where deptno = 30);
As there are six employees in deptno 30, the subquery returns six values.
It is something like
Select * from emp
Where sal > ( 2850, 1600, 1250, 1250,1500, 950 );
But, the above query will fail.
Operators like = , > , >= , < , <= , <> expects only one value in right hand side.
Here our subquery is returning 6 values. Hence the query will give us error.
Note:
For multiple row subqueries, we need to use multiple row operators.
There are three multiple-row operators
IN
ANY
ALL
First I want to discuss about ALL operator.
ALL Operator:
Select * from emp
Where sal >ALL ( select sal from emp
Where deptno=30);
When we run the subquery, we know it returns six values.
It is something like
Select * from emp
Where sal >ALL ( 1600, 1250, 2850, 1250,1500, 950 );
Do you think KING who is having sal 5000 is displayed?
Definitely YES, as 5000 is greater than all the six values returned by the subquery.
Do you think FORD who is having sal 3000 is displayed?
Definitely YES, as 3000 is greater than all the six values returned by the subquery.
Do you think CLARK who is having sal 2450 is displayed?
No, 2450 may be greater than 1600 and 1250 , but not greater than 2850.
We get the following four rows in the output, as they are having sal greater than all the six
values returned by the subquery.
ENAME
SAL
JONES
2975
FORD
3000
SCOTT
3000
KING
5000
Note: In other words, we get the above four rows are they are having sal greater than maximum value of the subquery.
ANY Operator
Select * from emp
Where sal >ANY ( select sal from emp
Where deptno=30);
When we run the subquery, we know it returns six values.
It is something like
Select * from emp
Where sal >ANY ( 1600, 1250, 2850, 1250,1500, 950 );
Do you think KING who is having sal 5000 is displayed?
Definitely YES, as 5000 is greater than any of the six values returned by the subquery.
Do you think CLARK who is having sal 2450 is displayed?
Definitely YES, as 2450 is greater than any of the six values returned by the subquery.
Do you think WARD who is having sal 1250 is displayed?
Definitely YES, as 1250 is greater than any of the six values returned by the subquery.
We get the following twelve rows in the output, as they are having sal greater than any of the six values returned by the subquery.
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7839
KING
PRESIDENT
11/17/1981
5000
10
7788
SCOTT
ANALYST
7566
12/9/1982
3000
20
7902
FORD
ANALYST
7566
12/3/1981
3000
20
7566
JONES
MANAGER
7839
4/2/1981
2975
20
7698
BLAKE
MANAGER
7839
5/1/1981
2850
30
7782
CLARK
MANAGER
7839
6/9/1981
2450
10
7499
ALLEN
SALESMAN
7698
2/20/1981
1600
300
30
7844
TURNER
SALESMAN
7698
9/8/1981
1500
0
30
7934
MILLER
CLERK
7782
1/23/1982
1300
10
7654
MARTIN
SALESMAN
7698
9/28/1981
1250
1400
30
7521
WARD
SALESMAN
7698
2/22/1981
1250
500
30
7876
ADAMS
CLERK
7788
1/12/1983
1100
20
Note: In other words, we get the above twelve rows are they are having sal greater than minimum value of the subquery.
IN Operator
In Operator will display the rows who are which are matching with the list of values provided.
Ex:
Select * from emp where job in ( CLERK, MANAGER);
In the output, we get all the employees who are CLERKs and MANAGERs.
Similarly,
Select * from emp
Where sal IN ( select sal from emp
Where deptno=30);
When we run the subquery, we know it returns six values.
It is something like
Select * from emp
Where sal IN ( 1600, 1250, 2850, 1250,1500, 950 );
We get the row who are having sal matching with any of the value of the subquery.
Multiple-column subquery:
When subquery returns more than one column, it is called multiple-column subquery.
IN operator is used with multiple-column subquery.
Ex:
Select * from emp
Where ( sal, job ) IN ( select sal, job from emp
Where deptno=30);
In the above example, as subquery is returning two columns, parent query should also compare both the columns. Pair-wise comparisons are done.
The following are the values returned by the subquery.
SAL
JOB
2850
MANAGER
1600
SALESMAN
1250
SALESMAN
1250
SALESMAN
1500
SALESMAN
950
CLERK
Now, the parent query will return the rows when combination of values are matching.
To be more clear, consider employee JONES
His sal and job values are
2975
MANAGER
JONES will not be displayed in the output, as combination of values ( 2975, MANAGER )
Is not present in the subquery.
We get the following output,
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7698
BLAKE
MANAGER
7839
5/1/1981
2850
30
7499
ALLEN
SALESMAN
7698
2/20/1981
1600
300
30
7654
MARTIN
SALESMAN
7698
9/28/1981
1250
1400
30
7521
WARD
SALESMAN
7698
2/22/1981
1250
500
30
7844
TURNER
SALESMAN
7698
9/8/1981
1500
0
30
7900
JAMES
CLERK
7698
12/3/1981
950
30
Co-related subquery
When subquery is executed in-relation to parent query, it is called co-related subquery.