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