Exists and NOT Exists in Oracle SQL

 

About the EXISTS Operator in Oracle SQL 

 

EXISTS
	EXISTS	Both can be used in the WHERE clause
	NOT EXISTS
		Both operators are used to determine whether a record exists in the table.
		EXISTS Returns TRUE/FALSE
		If the Sub-query returns at least one row	TRUE
		If the Sub-query returns 0 rows	FALSE
		NOT EXISTS Returns TRUE/FALSE
		If the Sub-query returns 0 rows	TRUE
		If the Sub-query returns at least one row	FALSE

 

Note:

Ø                                        Oracle recommends the EXISTS operator than IN operator because it gives good 
     performance than IN operator

Ø                       Oracle recommends the NOT EXISTS operator than NOT IN operator because it gives good 
     performance than NOT IN operator

 

Examples

Q: Display the departments which are not empty?

 SELECT *
     FROM DEPT  D
     WHERE EXISTS  (SELECT * FROM EMP WHERE DEPTNO = D.DEPTNO);-- SBQ should return TRUE

Result:-

DEPTNO	DNAME	LOC
20	RESEARCH	DALLAS	TRUE	1 ROW
30	SALES	CHICAGO	TRUE	1 ROW
10	ACCOUNTING	NEW YORK	TRUE	1 ROW
40	OPERATIONS	BOSTON	FALSE	0 ROW

 

Since Sub-Query(SBQ) returns FALSE instead of TRUE, the last record will not be fetched.

Using IN Operator

SELECT *
    FROM DEPT
    WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);

Result:-

DEPTNO	DNAME	LOC
20	RESEARCH	DALLAS
30	SALES	CHICAGO
10	ACCOUNTING	NEW YORK

 

Q: Display departments that are empty?

SELECT *
     FROM DEPT  D
     WHERE NOT EXISTS  (SELECT * FROM EMP WHERE DEPTNO = D.DEPTNO); --SBQ returns 0 rows

Result:-

DEPTNO	DNAME	LOC
20	RESEARCH	DALLAS	FALSE	1 ROW
30	SALES	CHICAGO	FALSE	1 ROW
10	ACCOUNTING	NEW YORK	FALSE	1 ROW
40	OPERATIONS	BOSTON	TRUE	0 ROW

 

Q: Find all employees who belong to a valid department that exists in the system.

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

Result:-

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	1-May-81	2850	NULL	30
7782	CLARK	MANAGER	7839	9-Jun-81	2450	NULL	10
7788	SCOTT	ANALYST	7566	9-Dec-82	3000	NULL	20
7839	KING	PRESIDENT		17-Nov-81	5000	NULL	10
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100	NULL	20
7900	JAMES	CLERK	7698	3-Dec-81	950	NULL	30
7902	FORD	ANALYST	7566	3-Dec-81	3000	NULL	20
7934	MILLER	CLERK	7782	23-Jan-82	1300	NULL	10

 

Q:Find all employees whose department number does not exist in the master department table.

select *from emp e
            where not exists(select 1 from dept d where e.deptno=d.deptno);

Result: No Data

 

Q: Find all departments that currently have zero employees assigned to them.

 

select *from dept d
            where not exists(select 1 from emp e where e.deptno=d.deptno);

 

DEPTNO	DNAME	LOC
40	OPERATIONS	BOSTON

 

Q: Explain the difference between IN and EXISTS Operators?

 

IN Operator	Vs	EXISTS Operator
Scans the inner subquery first, creates an in-memory list, and checks the outer table.		Scans the outer table first, and runs the subquery row-by-row until it finds a single match.
Requires a specific column (Ex: SELECT deptno).		Ignores the select list; typically uses a literal constant (Ex:SELECT 1).
Fails completely with NOT IN if the subquery returns even one NULL.		Works flawlessly with NOT EXISTS regardless of NULL values.
Small, static lists of literal values or small datasets.		Large datasets where the inner table contains many records.

 

Q: Explain the difference between EXISTS and NOT EXISTS?

EXISTS	Vs	NOT EXISTS
Semi-Join (Positive Check).		Anti-Join (Negative Check).
Subquery returns at least one row.		Subquery returns zero rows.
Stops scanning the instant it finds the first match (Early Exit).		Must scan until it finds a match, or scan the entire dataset to confirm zero matches exist.
Finding parents with children (Ex:-Departments with employees).		Finding orphans or missing links (Ex:-Departments with no employees).