8,127
========================
While a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.
Sub query can take value from outer query.
Ex#1
---------
Delete duplicate record from table
Expand|Select|Wrap|Line Numbers
- Delete from emp e where rowid>(select min(rowid) from emp where e.empno=empno)
Expand|Select|Wrap|Line Numbers
- Delete from emp
- where rowid not in(select max(rowid) from emp group by empno)
------------
Display first position of employee
Expand|Select|Wrap|Line Numbers
- select * from emp e where 0=(select count(distinct(sal)) from emp
- where sal>e.sal) order by sal desc
------------
Display details of employee whose sal greater than avg sal of it own department
Expand|Select|Wrap|Line Numbers
- Select * from emp e
- Where sal>(select avg(sal) from emp where deptno=e.deptno)
=======================
Inner query returns more than one row
Use multiple - row comparison operators
IN ----Equal to any member in the list
ANY--Compare value to each value returned by the sub query.
ALL---Compare value to every value returned by the sub query.
<ANY means less than the maximum
>ANY means more than the minimum
=ANY is equivalent to IN
>ALL means more than the maximum
<ALL means less than minimum.
The NOT operator can be used with IN, ANY, and ALL operators
Using IN
==========
Example #1
-------------------
Find the employees who earn the same salary as the minimum salary for departments.
Expand|Select|Wrap|Line Numbers
- SELECT * FROM emp
- WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno);
- is same as
- SELECT * FROM emp WHERE sal IN (800, 950, 1300);
=============
The ANY operator (and its synonym SOME operator) compares a value to each value returned by a sub query.
Example #2
--------------------------
Display employees whose salary is less than any clerk and who are not clerks.
Expand|Select|Wrap|Line Numbers
- SELECT * FROM emp WHERE sal < ANY ( SELECT sal FROM emp WHERE job= 'CLERK') AND job <> 'CLERK';
============
The ALL operator compares a value to every value returned by a subquery.
Example #3
--------------------------
Display details of employee whose sal greater than avg sal of all department
Expand|Select|Wrap|Line Numbers
- SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal) FROM emp GROUP BY deptno);
Note :--Please Try on Scott Schema
Using In line sub query(inline views)
==============================
Ex#1
----------
Display Odd number records.
Expand|Select|Wrap|Line Numbers
- SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp) temp
- WHERE MOD(temp.rn,2) = 1
----------
Display manager and its salary
Expand|Select|Wrap|Line Numbers
- SELECT A.MGR, B.SAL FROM (SELECT DISTINCT MGR FROM EMP
- WHERE MGR IS NOT NULL) A LEFT OUTER JOIN
- (SELECT EMPNO, SAL FROM EMP) B ON A.MGR=B.EMPNO
----------
Top three employee who earn hightest salary.
Expand|Select|Wrap|Line Numbers
- select * from (select * from emp order by sal where rownum<4
----------
Query for listing Deptno, ename, sal, SUM(sal in that dept) :
Expand|Select|Wrap|Line Numbers
- SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a ORDER BY deptno
----------
Display manager no and name who has more employee
Expand|Select|Wrap|Line Numbers
- select empno,ename from emp where empno=(select mgr from
- (select mgr,count(empno) from emp where mgr is not null group by mgr
- order by count(empno) desc) where rownum<2)
