Single Row Sub Query:
Ø These queries return only one row
from the inner select statement.
Multiple Row Sub Query:
Ø These Queries return more than one
row from the inner select statement.
Multiple Column Sub Query:
Ø These queries return more than one
column from the inner select statement.
Guidelines to Follow:
Ø A sub query must be enclosed in
parenthesis.
Ø A sub query must appear on the right
side of the comparison operator only.
Ø Sub queries should not contain an
order by clause.
Ø Only one order by clause can be
implemented for the total select statement.
Ø Two classes of comparison operators
can be used in sub queries they are…
o
Single
row operators
o
Multiple
row operators
Let Us Start with Single row sub queries:
Ex:
- Select Ename, Sal, Job From Emp
Where Sal > (select Sal From Emp where Empno = 7566 );
- Select Ename, Sal, Job From Emp
Where Job = ( Select Job From Emp Where Ename = upper(’smith’) );
- Select Empno, Ename, Hiredate,
Sal From Emp where Hiredate > ( Select Hiredate From Emp where Ename =
’TURNER’) order by Sal;
- Select Empno, Ename, Sal, Job
From Emp where Deptno = (Select Deptno From Dept Where Dname = ’SALES’);
- Select Empno, Ename, Sal, Comm,
Sal + NVL(Comm, 0) From Emp Where Deptno = (Select Deptno From Dept where
Loc = ’DALLAS’);
Applying Group Functions In Sub Queries:
Ø The data from the main query can be
displayed by using a group function in a sub query.
Ø As a group function returns a single
row, the query passes through the success state.
Ø The inner sub query should not have
a group by clause in this scenario.
Ex:
- Select Ename, Job, Sal From Emp
where Sal = (select Max(sal) from Emp );
- Select Ename, Job, Sal From Emp where sal =
(Select Min(sal) from Emp);
- Select Ename, Job, Sal From Emp
where Sal < (Select STDDEV(sal) From Emp);
Applying Having Clause With Sub Queries:
Ø A sub query can be also applied in
having Clause.
Ø The Oracle server executes the sub
query, and the results are returned into the having clause of the main query.
Ø The inner query need not use any group
functions in this scenario.
Ø The outer queries having clause
contains group function.
Ex:
- Select Deptno, Min(Sal) From
Emp Group by Deptno having Min(Sal) > ( Select Min(sal) From Emp where
Deptno = 20 );
- Select Job, AVG(Sal) From Emp
Group by Job Having AVG(Sal) = ( Select Min(AVG(Sal)) From Emp Group by
Job);
- Select Job, AVG(Sal) From Emp
Group by Having AVG(Sal) < ( Select Max(AVG(sal)) From Emp Group by Job
);
- Select Job, AVG(Sal),
To_char(AVG(Sal), ’L99,999.99’) From Emp Group by Job Having AVG(Sal) <
( Select Max(Avg(Sal)) From Emp Group by Deptno);
Sub Queries Returning More Than One Row:
Ø The Sub Queries that return more
than one row are called as multiple row sub queries.
Ø In this case a multiple row operator
should be used.
Ø The multiple row operators expect
one or more values as arguments.
Ex:
- Select Ename, Sal, Deptno From
Emp where Sal IN (Select Min(Sal) From Emp Group by Deptno );
- Select Ename, Sal, Deptno From
Emp where Sal IN ( Select Max(Sal) From Emp Group by Deptno);
- Select Ename, Sal, Deptno, Job
From Emp where Sal IN (Select Max(Sal) From Emp Group by Job);
ANY Operator:
Ex:
- Select Empno, Ename, Job From
Emp where Sal < ANY (Select Sal From Emp where Job = ’CLERK’ );
- Select Empno, Ename, Job, Sal
From Emp where Sal < ANY ( Select Sal From Emp where Deptno = 20 and
Job <> ’CLERK’);
Note: < ANY Means Less than the
Maximum Value in the List.
- Select Empno, Ename, Job From
Emp where Sal > ANY(Select Sal From Emp where job = ’CLERK’);
Note: > ANY Means More Than The
Minimum Value in the List.
- Select Empno, Ename, Job From
Emp where Sal = ANY(select Sal From Emp where job = ’CLERK’);
Note: =ANY it is equivalent
to IN Operator.
ALL Operator:
- Select
Empno, Ename, Job ,Sal From Emp where sal > All(select AVG(Sal) From
Emp Group by
Deptno);
Note: > ALL à It means more than the maximum in
the list.
- Select Empno, Ename, Job, Sal
From Emp where Sal < All(select AVG(Sal) From Emp Group by Deptno);
Note:
< ALL à
It means less than the minimum in the list.
Sub Queries Returning Multiple Columns:
Ø In sub queries multiple columns can
be compared in the where clause, by writing a compound where clause using
logical operators.
Ø Multiple column sub queries enable
us to combine the duplicate where condition into a single where clause.
Syntax:
Ø Select Column1, Column2,… From
TableName where ( column a, column b, … ) IN (Select Column a, Column b, … From
TableName where condition );
Ø The column comparisons in a multiple
column sub queries can be
o
Pair
wise comparison.
o
Non
pair wise comparison.
Ø In pair wise comparison each
candidate row in the select statement must have both the same values associated
with each column in the group.
Ø The Non pair wise comparison is also
called cross product, we can use a where clause with multiple conditions.
Ø In non pair wise comparison, the
candidate row must match the multiple conditions in the where clause but the
values are compared individually.
Pair wise Comparison OR Compound where Clause
Based Sub Query:
- Select
OrdID, ProdID, Qty From where (ProdID, Qty) IN(Select ProdId,Qty From Emp
Item where OrdID = 605 ) AND OrdID <> 605;
Non Pair wise Comparison OR Component where
clause Based Sub Query:
- Select OrdID, ProdID, Qty From Item where
ProdID IN (Select ProdID From Item where OrdID = 605 ) AND Qty IN (Select
Qty From Item where OrdID = 605 ) AND OrdID <> 605;
Handling NULL Values in Sub Queries:
Ø If one of the values returned by the
inner query is null value, then the entire query returns NO ROWS.
Ø All conditions that compare a null
value result in a null.
Ø Whenever a null could be part of a
sub query, it is better not to use NOT IN operator as it is equivalent to !=ALL
operator.
Ex:
- Select
E.Ename From Emp E where E.Empno IN ( Select M.Mgr From Emp M);
Applying Sub Query in From Clauses:
Ø A Sub query in from clause is
equivalent to a view.
Ø The sub query in from clause defines
a data source for that particular select statement and only that select
statement.
Ex:
- Select
E.Ename, E.Sal, E.Deptno, E1.SalAvg From Emp E, (select Deptno, AVG(Sal)
SalAvg From Emp Group by Deptno) E1 where E.Deptno = E1.Deptno And E.Sal
> E1.SalAvg;
- Select
T1.Deptno, Dname, Staff From Dept T1, (Select Deptno, Count(*) As Staff
From Emp Group by Deptno) T2 where T1.Deptno = T2.Deptno AND Staff >=
5;
- Select
Deptno, Sum(sal), Sum(Sal)/Tot_sal * 100
“Salary%” From Emp, (select Sum(Sal) Tot_sal From Emp ) Group by
Deptno, Tot_sal;
- Select
E.EmpCount, D.DeptCount From (Select Count(*) EmpCount From Emp ) E,
(Select Count(*) DeptCount From Dept ) D;
- Select
E.EmpCount, D.DeptCount, S.GradeCnt, E.EmpCount + D.DeptCount + S.GradeCnt
TotalRecCnt From Emp, ( Select Count(*) EmpCount From Emp ) E, (Select
Count(*) DeptCount From Dept ) D, (Select Count (*) GradeCnt From
SalGrade) S;
- Select
A.Deptno “Department Number”, (A.NumEmp /B.TotalCount) * 100 “%Employees”,
(A.SalSum / B.TotalSal) * 100 “%Salary” From (Select Deptno, Count(*)
NumEmp, Sum(Sal) SalSum From Emp Group by Deptno ) A, (Select Count(*)
TotalCount, Sum(Sal) TotalSal From Emp ) B;
Sub Select Statements:
Ø These are Select Statements Declared
as Part of the Select List.
Ex:
- Select
Ename, Sal, (Select AVG(Sal) From Emp ) “Organization Average” From Emp;
- Select
Ename, Sal, (Select Max(sal) From Emp ) “Organization Maximum”, (Select
Min(Sal) From Emp ) “organization Minimum” From Emp;
Correlated Sub Queries:
Ø It is another way of performing
queries upon the data with a simulation of joins.
Ø In this the information from the
outer select statement participate as a condition in the inner select
statement.
Syntax:
- Select
Selectlist From Table1 F_Alias1 where Expr.Operator (select Selectlist
From Table2 F_Alias2 where F_Alias1.Column Operator F_Alias2.Column);
Steps Performed:
Ø First the outer query is executed.
Ø Passes the qualified column value to
the inner queries where clause.
Ø Then the inner query or candidate
query is executed, and the result is passed to the outer queries where clause.
Ø Depending on the supplied value the
condition is qualified for the specific record.
Ø Successful presented else suppressed
from display
Ex:
- Select
Empno, Ename, E.Deptno, Sal, MGR From Emp E where E.Sal > ANY (Select
M.Sal From Emp M where M.Empno = E.MGR );
- Select
Deptno, Dname From Dept D where EXISTS (Select * from Emp E where D.Deptno
= E.Deptno);
- Select
Deptno, Dname, From Dept D where NOT EXISTS (select * From Emp E where
D.Deptno = E.Deptno);
- Select
E.Ename From Emp E where EXISTS (Select * From Emp E1 where E1.Empno =
E.MGR);
- Select
E.Ename From Emp E where NOT EXISTS (Select * From Emp E1 where E1.Empno =
E.MGR);
- Select
E.Ename From Emp E where NOT EXISTS ( Select * From Emp E1 where E1.Mgr = E.
Empno);