sub queries

 

SUB QUERIES

Types of queries

1.Root query

2.Parent query or outer query or main query

3.Child query or inner query or sub query

 

1.Root query

à The query which is not dependent on another query is called root query

à Select ename, sal from emp where deptno=10;

 

2.Parent query

à The query which depends on any another query is called Parent query

à Select ename, sal from emp where deptno=(select deptno from dept where deptno=10);

à In the above ex 1^st select stmt is Parent query.

3.Child query

à The query which provides values to the parent query is called Child query.

à The sub query in the where clause is called nested sub query, the sub query in the from clause is called inline view.

à The sub query can be part of a column in the select stmt.

Select e.ename, e.sal, e.deptno, (select dname from dept d where d.deptno=e.deptno) dname from emp e;

à There is no limit the no of sub queries in the from clause, we can have up to 250 sub queries in where clause.

à Select ename, sal from emp where deptno=(select deptno from dept where deptno=10);

à In the above ex 2^nd  select stmt is sub query.

 Single row sub query operators are >, =, >=, <, <=, <>

 Multi row sub query operators are in, any, all

Single row sub query

Guidelines for using sub queries

à Place sub query on right side of comparison operator.

à Order by clause in the sub query is not needed.

Example for simple single row sub query

à Select empno, ename, sal, deptno from emp where sal<(select sal from emp where empno=7566);

Apply group function in sub query

à Select empno, ename, sal, deptno from emp where sal=(select max(sal) from emp);

Apply having clause in sub query

à Oracle server 1^st execute the sub query and it passes to having clause in main query

à Select deptno, min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);

Sub query with update statement

à Update emp set deptno=(select deptno from emp where empno=7521) where ename=’SADA’;

Sub query with delete statement

à Delete from emp where sal =(select sal from emp where empno=7369);

Multi row sub query

IN: - it means equal to any row in sub query

à Select ename, sal, deptno from emp where sal in (select max(sal) from emp group by deptno);

SOME/ANY: - compare the value to each value obtain by sub query

‘<any/some’ means less than the max value in the list

‘>any/some’ means greater than the min value in the list

à select ename, sal, deptno from emp where sal<some (1250,1500,1600);

à select ename, sal, deptno from emp where sal>any (select sal from emp where sal<1600);

ALL: - compare the value to each value obtain by sub query

‘<ALL’ means less than the min value in the list

‘>ALL’ means greater than the max value in the list

à select ename, sal, deptno from emp where sal<ALL (select sal from emp where sal>1600);

à select ename, sal, deptno from emp where sal>ALL (select sal from emp where sal<1600);

Sub query writing with multiple columns

à Multiple columns can compare in the where clause by using logical operators.

   Types

1.Pair wise comparison: -

à Each row in the select statement must have both same values associated with each column in the group.

à Select ename, sal, deptno from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno) and deptno<>10;

2.Non Pair wise comparison: -

à Each row in the select statement must match multiple conditions in the where clause but the values are compared individually.

à Select ename, sal, deptno from emp where deptno in (select deptno from emp group by deptno) and sal in (select max(sal) from emp group by deptno) and deptno<>10;

Apply sub query in from clause

à It is equal to a VIEW and it is called inline view.

à Select d.deptno, d.dname, v.vcnt from dept d, (select deptno, count(*) vcnt from emp group by deptno) v where d.deptno=v.deptno;

Sub select statement

à Select ename, sal, (select max(sal) from emp) from emp;

Co related sub query

à In this parent query processed 1^st and passes the qualified column values to sub query in where clause.

à EXISTS: - it returns true if sub query is success otherwise false.

à Select e.empno, e.ename, e.sal, e. job, e.deptno from emp e where deptno=10 and exists (select count(*) from emp where deptno=e.deptno and job=’ANALYST’ group by job having count(*)<5);

Note: - if sub query is true then it written main query result, otherwise it written NULL value as result.

    Please watch and subscribe my YOUTUBE Channel

           Kamal SQL CLASSES in Telugu