Types of Sub Queries

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);