SQL Sub-Query

Sub-Query

Types of Sub query:-In the basis of place of the subquery, there are three types

1. Nested Subquery:- The subquery appears in the WHERE clause of the SQL.
Example:- select * from emp where deptno in (select deptno from dept);
2. Inline View: The subquery appears in the FROM clause (in place of table name) of the SQL.
Example:- SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a,
          (SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b
          WHERE a.deptno = b.deptno AND a.sal > b.salavg;
3. Scalar Subquery: The subquery appears in the SELECT clause of the SQL.
Example:-
select a.*,(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno) avg_sal
from emp a
where a.sal>(select round(avg(b.sal),2) from emp b where a.deptno=b.deptno);


In the basis of execution of subquery, there are two categories of subqueries

1. Simple Subquery: A simple subquery can be exceuted independently.
                    It is executed first then parent query executed on the basis of Subquery result.
Example:-select * from emp where deptno in (select deptno from dept);
2. Correlated Subquery: A correlated subquery can not be excuted independently.
                       In Correlated Subquery, parent query executed first then Correlated Subquery
                       executed on the basis of parent query record.                      
Example:-select a.* from emp a where a.sal>(select avg(b.sal) from emp b where a.deptno=b.deptno);

Multiple-Column Subquery:-
Syntax:-
SELECT ,,... FROM 
WHERE (,, ...) IN (SELECT ,,... FROM
WHERE condition);

Example:- Display the employee's details whose job and deptno matched with 'WARD'
          and  'WARD' details should not be displayed

Pair-wise Comparison:-
select * from emp where (job,deptno) in (select job,deptno from emp where ename='WARD')
and ENAME<>'WARD';

Non Pair-wise Comparison:-
select * from emp
where (job) in (select job from emp where ename='WARD')
and (deptno) in (select deptno from emp where ename='WARD')
and ENAME<>'WARD';

Sub query in FROM clause (Inline View or Logical Table):-
Example:-
SELECT  a.ename, a.sal, a.deptno, b.salavg FROM emp a,
(SELECT  deptno, avg(sal) salavg FROM emp GROUP BY deptno) b
WHERE a.deptno = b.deptno AND a.sal > b.salavg;


Question for Practice :-

Q1 :- What is the Sub-Query ?
Q2 :- What are types of Sub-Query ?
Q3 :- Why Sub-Query is required ?
Q4 :- What is the difference between Main Sub-Query and Co-related Sub-Query ?
Q5 :- Can we use Sub-Query in place of Column Name as well ?
Q6 :- What is the In-line View ?