Sub Queries in Oracle SQL and their types

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

Advertisements

4 thoughts on “Sub Queries in Oracle SQL and their types

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s