Sub Query

 “Sub Query is a concept or mech. Implement on select statement whenever a condition values is unknown to the user.”

In other words Sub-Query means nested query.
Sub-query has two diff parts or blocks
1.  Inner query
2.  Outer query
        Inner query is a query which is responsible to evaluate the condition values and provide the output to outer query is responsible to display the output.

TYPES of sub-query
        Depending on the value return from inner to outer query, sub query is broadly categorized in two diff types
i.    Single row sub-query
ii.  Multi  row         ,,
        Single row sub-query is a type of sub-query where inner query return one value to the outer query for processing.
Eg display those records that sal is greater than ‘smith sal’.
        Whereas multi row sub query is a type of sub-query. Where inner query returns more than value to the outer query for processing.



Syntax:
Select<exp>
From <t_name>
Where <col_name> operator ( select <col_name> from <t_name> where <cond>);
        The syntax of single row sub query and multi row sub query is same but user can identify a sub query through its operator S.R.S.Q operators are
<,>,<=,>=,<>,!=,=             
 where as multi row sub query operator are
in, =any, >any, <any, >all, <all,

ex -  Single row sub query
select * from emp
where sal>(select sal from emp
                      where ename=’smith’) 
and
job <> (select job from emp
               where ename=’frod’ and ename=’scott’);
EX – multi row  sub query
Select * from emp
Where sal>(sselect sal where ename=’allen’)
And job =any(select job where deptno = 30)
And deptno=30;

NOTE:

        That inner sub query is a select statement but it does not support order by clause to arrange the value in a proper manner.