Subquries

Types of Subquries:-

     Single Row sub queries: - return zero rows or one row to the outer SQL stmt.There is a special case of single row that contains exactly one column, this type of sub queries are called as scalar sub query.

Multiple row sub queries: - return one or more rows to the outer SQL statement

In addition there are three subtypes of sub queries that may return single row or multiple rows.

Multiple – column sub queries :- return more than one column to the outer SQL stmt

Correlated sub queries :- reference one or more columns in the outer SQL statement .These are called correlated sub queries because they are related to the outer SQL stmt through the same columns.

Nested sub queries :-  are placed within another sub query. you can nest sub queries to depth of 255.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

     Single Row sub queries: -

                              

1)      select name,salary from emp where empno = (select empno from emp where name = ‘RAJESH’);

2)      select product_id,name ,price from products where price > (select avg(price) from products );

3)      select product_type_id,avg(price) from products group by product_type_id having avg(price) < (select max(avg)price)) from products group by product_type_id order by product_type_id;

4)      sub queries in from clause(Inline Queries)

        

        select product_id from (select procust_id from products where product_id <3);

        select prds.product_id,price,purchase_data.product_count from products prds,

                  (select product_id ,count(product_id) product_count from purchases

                    Group by product_id) purchase_data

        Where prds.product_id =  purchase_data.prodcut_id;

Here the sub query is just another source of data to the from clause of the outer query.

Errors you might encounter

    Single row sub queries may return maximum of one row . If your sub query return one row you will get the following error

ORA-01427 single-row subquery return more than one row.

    Sub queries may not contain an order by clause . instead any ordering must be done in the outer query .for example

Select product_id ,  name , price fromm products where price > (select avg(price) frin products )order by product_id desc;

Multiple row sub queries

     You use a multiple – row sub query to return one or more rows to outer SQL stmt.TO handle this case your outer query may use IN,ANY,or ALL operator.you can also use the EXISTS operator to check of a value is in a list returned by a correlated sub query.

IN:-

1)   Select product_id,name from products where product_id IN (select product_id from products from products where name like ‘%Laptop%’);

 2)  Select product_id,name from products where product_id NOT IN (select product_id from products from products where name like ‘%Laptop%’);

ANY:-

1)      select empno,nme from emp where salary < ANY (select low_salary from salary_grades);

      ALL:-

         Select empno,name form emp where salary > ALL(select high_salary form salary_grades);

        Results :-  no rows selected

        Multiple Column sub queries :-

 

             Select product_id ,product_type_id name ,price from products where (product_id,price ) IN    (select product_id,MIN(price) from products group by product_type_id);

     Note that sub query returns more than one column .

Correlated Subquries:-

     Correlated sub queries references one or more columns in the outer SQL stmt.

You typically use a correlated sub queries when you need an answer to a question that depends on a values in each row contained in an outer query.For example you might want to see whether there is a relationship between the data but don’t care how many rows are returned by the sub query.

That is you just wan tot check whether any rows are retrned but you don’t care how many.

    Correlated sub query us run in once for each row in the outer query: this is different from non – correlated sub query , which is run once prior to running the outer query.in addition a correlated sub query can resolve null values.

Select product_id ,produt_type_id,name,price

From products outer

Where price >

   (select avg(price) from products inner where inner.product_type_id = outer.product_type_id);

Using Exists ,Not Exists

   Select empno,name

   From emp

    Where exists

               (select emono from emp inner

                 Where inner.manager_num = outer.empno);

Or

   Select empno,name

   From emp

    Where exists

               (select 1 from emp inner

                 Where inner.manager_num = outer.empno);

Exists and Not Exists Versus In and NOT IN

    Exists is different from IN : Exists checks just for the existence of rows ,where as IN check for actual actualvalues

  Exists typically offers better performance than IN with subqueries ,therefore you should use exists rather than IN wherever possible.

You should be careful when using Exists /Not Exists and IN  /Not IN . When a list of values contains a null value ,Not exists returns true ,but Not In returns false.

For example :-

Query to fetch the product types that don’t have any products of that type in the products table.

Select product_type_id,name

From product_type outer

Where Not Exists

( select 1 from products inner

    Where inner.product_type_id = outer.product_type_id);

Product_type_id       name

---------------------      -----------------------

5              Computers

Notice one row is returned one row

When you use Not in :

Select product_type_id,name

From product_type outer

Where Not IN

( select product_type_id  from products);

No rows returned

Because the subquery returns a list of product_id values,one of which is null.

When can overcome this problem using NVL

Select product_type_id,name

From product_type outer

Where Not IN

( select nvl(product_type_id ,0) from products);

Product_type_id       name

---------------------      -----------------------

5          Computers

Nested Subquereis

  You can nest sub queries inside the other sub queries to depth of 255.

Select product_type_id,avg(price)

From products

Group by product_type_id

Having avg(price) <

(select max(avg(price))

 From products

Where product_type_id in

   (select product_type_id

    From purchase

Where quantity >1

Group by product_typ_id)

Order by product_typ_id;

We can write upodate and delete statements containing subqueries

  Update emp

  Set salary =

       (select avg(high_salary)

          From salary_grades)

Where empno =4;

   Delete from emp

Where salary >

   Select avg(slary) from salary_grades);