EXISTS example

View All Scripts Login to Run Script

Script Name 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.
Area SQL General / SQL Query
Contributor Oracle
Created Monday October 24, 2016

Statement 1

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

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


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7566 JONES MANAGER 7839 02-APR-81 2975  - 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850  - 30
7782 CLARK MANAGER 7839 09-JUN-81 2450  - 10
7788 SCOTT ANALYST 7566 19-APR-87 3000  - 20
7839 KING PRESIDENT  - 17-NOV-81 5000  - 10
7902 FORD ANALYST 7566 03-DEC-81 3000  - 20

6 rows selected.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	-	10
7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
7839	KING	PRESIDENT	-	17-NOV-81	5000	-	10
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20

Additional Information

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