Sub-Query
Types of Sub query:-In the basis of place of the subquery, there are three types1. 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 ?
