EXISTS Operator

Where exists (subquery)

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written with a standard join to improve performance.

Where not exists (subquery)

The WHERE NOT EXISTS subquery is used to display cases where a selected column does not appear in another table.

As a general rule, the use of the NOT EXISTS subqueries are discouraged because the query can often be re-written as a standard join with much faster performance.

Some related examples.

1. Use EXISTS to link two queries.

SQL> SELECT * FROM emp WHERE EXISTS
  2  (select * from dept where dept.deptno = emp.deptno);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
---------- ---------- --------- ---------- --------- ---------- 
      7934 MILLER     CLERK           7782 23-JAN-82       1300 
      7839 KING       PRESIDENT            17-NOV-81       5000 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450 
      7902 FORD       ANALYST         7566 03-DEC-81       3000 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000 
      7566 JONES      MANAGER         7839 02-APR-81       2975 
      7369 SMITH      CLERK           7902 17-DEC-80        800 
      7900 JAMES      CLERK           7698 03-DEC-81        950 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL 
---------- ---------- --------- ---------- --------- ---------- 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 

2. The EXISTS condition can also be combined with the NOT operator.

SQL>     SELECT *
  2      FROM emp
  3      WHERE not exists (select * from dept 
  4                        Where dept.deptno = emp.deptno);

3. Test the NOT EXISTS version.

SQL> SELECT cust_no, lastname
  2    FROM customer c
  3    WHERE NOT EXISTS
  4    (SELECT cust_no FROM ord
  5     WHERE cust_no = c.cust_no);
   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

4. Display the customer number and last name of all customers with no orders.

SQL> select cust_no, lastname
  2    from customer c
  3    where not exists
  4    (select * from ord o where o.cust_no = c.cust_no);

   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.