What is a Sub Query?
A query nested inside another query and it is used when a query is based on an unknown value. A sub query is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query the sub query must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.
What are the types of Sub Queries?
Single Row Sub Query
Return the outer query one row of results that consists of one column
Multiple Row Sub Query
Return to the outer query more then one row of the results it Require use of IN, ANY, ALL, or EXISTS operators
Multiple Column Sub Query
Return to the outer query more then one row of the results in this type of query Column list on the left side of operator must be in parentheses it use the IN operator for WHERE and HAVING clauses
Correlated Sub Query
Reference a column in the outer query and execute the sub query once for every row in outer query
Uncorrelated Sub Query
Execute the sub query first and passes the value to outer query
Source Oracle Introduction to Database
Oracle Application World 
wht abt inline view?? i tink it is also sub query
Think So
in case of correlated subquery how flow of execution of query takes place
hi friends,
in my knowledge there are four kinds of sub query’s
1)inline sub query
2)scalar sub query
3)single and multiple row sub query
4)correlated sub query
in inline sub query we are representing it inside the from clause
in case of sql it is hard to deal with group functions and grouping those
here we can represent it inside from clause and we can do our manipulations inside that
eg:
select deptno,maxsal from (select deptno,max(sal) as maxsal from emp);
scalar queries can return only one raw
we are oftenly using this like the follows
select deptno,name,(select last_name from emp where name=’shibin’) from emp where rownum=1;
in case of 3rd query we are using inner queries inside in,like,not in,exists,not exists,all relational operators and here the output should be like(the flow of outer query depends on the inner queries returned value)
correlated sub queries are the one in which the inner query depend on the value from outer query