Nested Queries or Subqueries | Readstall

Nested Queries or Subqueries

06-02-19 Sunandana M 0 comment

Nesting of queries one within the other is termed as sub query.A statement containing a sub_query is called a parent query.Subqueries are used to retrieve data from tables that depend the values on the table itself.There  are various types of  subqueries.

  • Single _row subquery
  • Multi_row subquery
  • Multiple subquery
  • Correlated subquery
  • Exists
  • Not exists

Single _row subquery:

                    In single row subquery only one value will be returned.

                                  Eg:-select * from emp where sal>(select sal from emp where empno=121);

Multi _row subquery:

                   In multi_row subquery more than one value will be returned.In such cases we include operators like (any,all,in,or,not in)in between the comparision operators and the subquery.

                                  Eg:-select * from emp where sal>any(select sal from emp where sal between

                                            2000 and 5000);

Multiple subquery:

                   In multiple subquery there no limit on the number of subqueries included in the where clause it allows nesting of a query with in subquery.

                                Eg:-select * from emp where sal=(select  max(sal) from emp where sal<(select

                                            max(sal)  from emp));

correlated subquery:

                   A subquery is evaluated once for the entire parent statement where as the correlated subquery is evaluated for once for every row processed for the parent statement.

                             Eg:-select distinct deptno from emp where 2<=(select count(ename) from emp

                                                   where emp.deptno=deptno);

 

Exists:

                Exists function is a test for existence.This is a logical test for the return of rows from a query.

                          Eg:-suppose we want to display a deptno which has more than 3 employees.

                                 Sql>select deptno,count(*) from emp group by deptno having count(*)>3

                                 From this query if you want to display the names of employees.

                                Sql>select deptno,ename,count(*) from emp group by deptno,ename

                                having  count(*)>3; 

             The above query returns nothing because the combination of deptno,ename never returns morethan one     count.Hence the solution is make use of exists.       

                           Eg:-select deptno,ename from emp e1 where exists(select * from emp e2 where

                                    e1.deptno =e2.deptno group by e2.deptno having count(e2.ename)>3) order by                                                          deptno,ename;

Not Exists:

               Eg:-select * from emp e1 where not exists(select * from emp e2 where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename)>3) order by deptno,ename;

               The exists operator is usually used with correlated subqueries this operator enables to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the inner query.

               If the subquery returns atleast one row then the operator return true.Otherwise if the value doesn’t returns it result false.                                                                                                                                     



Leave a reply