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 operatorS.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.