Exits

EXISTS -> is useful to find out the outer query value exist in the

inner query or not atleast once. When for the outer query if match value exist in the inner

query, inner query returns TRUE and then outer query displays that row, if for

outer query match value doesn't exist in the inner query then inner query returns

FALSE and then outer query doesn't display that row.

NOT EXISTS -> will return result opposite to the exists.

===========

SELECT * FROM dept;

select d.* from dept d where EXISTS

(select e.empno from emp e where e.deptno = d.deptno);

select d.* from dept d where EXISTS

(select 1 from emp e where e.deptno = d.deptno);

select d.* from dept d where EXISTS

(select 'X' from emp e where e.deptno = d.deptno);

select d.* from dept d where NOT EXISTS

(select e.empno from emp e where e.deptno = d.deptno);

select d.* from dept d where exists

(select e.empno from emp e where e.deptno = d.deptno-10);

select d.* from dept d where exists

(select e.empno from emp e where e.deptno+10 = d.deptno-10);

select d.* from dept d where exists

(select e.empno from emp e where e.deptno+10 = d.deptno+10);

select * from dept d where exists

(select NULL from emp e where e.deptno = d.deptno);

select * from dept d where not exists

(select 9 from emp e where e.deptno = d.deptno);

select * from dept d where not exists

(select null from emp e where e.deptno = d.deptno);

SET TIMING ON

SELECT m.last_name FROM hr.employees m WHERE m.employee_id IN(

SELECT e.manager_id FROM hr.employees e);

SET TIMING ON

SELECT m.last_name FROM hr.employees m WHERE EXISTS (

SELECT 1 FROM hr.employees e WHERE m.employee_id=e.manager_id);