NOT EXISTS example

View All Scripts Login to Run Script

Script Name NOT EXISTS example
Description An EXISTS condition tests for existence of rows in a subquery. If at least one row returns, it will evaluate as TRUE. NOT EXISTS evaluates as TRUE if 0 rows are returned and can be used to validate the absence of a condition.
Area SQL General / SQL Query
Contributor Oracle
Created Monday October 24, 2016

Statement 1

This returns the employees (in the EMP table) that are NOT managers. It checks for their employee number as a manager (mgr column) and returns them if they are not found.

select * from scott.emp e1    
 where not exists ( select null from scott.emp e2 
                     where e2.mgr = e1.empno )


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7934 MILLER CLERK 7782 23-JAN-82 1300  - 10
7369 SMITH CLERK 7902 17-DEC-80 800  - 20
7876 ADAMS CLERK 7788 23-MAY-87 1100  - 20
7900 JAMES CLERK 7698 03-DEC-81 950  - 30

8 rows selected.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10
7369	SMITH	CLERK	7902	17-DEC-80	800	-	20
7876	ADAMS	CLERK	7788	23-MAY-87	1100	-	20
7900	JAMES	CLERK	7698	03-DEC-81	950	-	30

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database