ORACLE Exists usages with an example


In this post, I will explain usages of EXISTS condition;

First, EXISTS condition is used to filter the main data (resultset) based on the other query coded by application developers.

By using EXISTS, resultset displays all matching rows returned by both queries. However, keep in mind that this could be also accomplished by coding a standard JOIN syntax.  Let me explain with a simple example; I have 2 tables;

 EMPLOYEES and SUCCESSFUL_EMPLOYEES  tables

EMPLOYEES TABLE

EMPLOYEE_ID NUMBER
  FIRST_NAME VARCHAR2
  LAST_NAME VARCHAR2
  EMAIL VARCHAR2
  PHONE_NUMBER
  HIRE_DATE
  JOB_ID
  SALARY
  COMMISSION_PCT
  MANAGER_ID
  DEPARTMENT_ID
  ACTIVE –> stores if the employee is working or not (default = Y otherwise = N)

  SUCCESSFUL_EMPLOYEES  TABLE–> stores  successful employees and their projects in which they are successful    

  SUCCESSFUL_EMPLOYEE_ID NUMBER 
  EMPLOYEE_ID NUMBER
  PROJECT_ID NUMBER
  PROJECTDESC VARCHAR2 
  SELECTEDDATE DATE 
  COMMENTS

The first query returns the employees who are succesful on various projects

SELECT SUCCESSFUL_EMPLOYEE_ID, EMPLOYEE_ID, PROJECT_ID, PROJECTDESC 
FROM SUCCESSFUL_EMPLOYEES

SUCCESSFUL_EMPLOYEE_ID EMPLOYEE_ID PROJECT_ID PROJECTDESC
1 130 1 SQL Workshop
2 131 2 SQL Workshop2
3 132 2 SQL Workshop2
4 138 2 SQL Workshop2
5 142 2 SQL Wworkshop2

Just test if these employees are active with a simple query;

SELECT EMPLOYEE_ID, FIRST_NAMELAST_NAME, ACTIVE
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (130,131,132,138,142);

And the result is;

EMPLOYEE_ID FIRST_NAME LAST_NAME ACTIVE
130 Mozhe Atkinson N
131 James Marlow N
132 TJ Olson N
138 Stephen Stiles Y
142 Curtis Davies Y

Now, I want rewrite the query and get the  firstname and lastname of employees who are active (working now) with
the help of EXISTS condition

This could be accomplished by writing a EXISTS condition

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME –> This query will run first
FROM EMPLOYEES
WHERE ACTIVE = ‘Y’
AND EXISTS (
 SELECT 1 FROM SUCCESSFUL_EMPLOYEES –> This query will run after the first query
 WHERE EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
)

Oracle will execute (parsing or changing) the above query like below;

FOR ALL_ACTIVE_EMPLOYEES IN
(SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE ACTIVE = ‘Y’ )
   LOOP
      IF( EXISTS ( SELECT 1 from
                               SUCCESSFUL_EMPLOYEES
                              WHERE EMPLOYEE_ID = ALL_ACTIVE_EMPLOYEES.EMPLOYEE_ID )
      THEN                                                                
         OUTPUT THE RECORD
      END IF
   END IF

And the result is;

EMPLOYEE_ID FIRST_NAME LAST_NAME
138 Stephen Stiles
142 Curtis Davies

Lets rewrite the query with a standard JOIN syntax

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME
FROM EMPLOYEES, SUCCESSFUL_EMPLOYEES
WHERE EMPLOYEES.EMPLOYEE_ID = SUCCESSFUL_EMPLOYEES.EMPLOYEE_ID
AND EMPLOYEES.ACTIVE = ‘Y’;

And the result is same as the above one;

EMPLOYEE_ID FIRST_NAME LAST_NAME
138 Stephen Stiles
142 Curtis Davies
  • EXISTS condition could be used whenever a standard JOIN syntax is not available to code such as predefined reports having only one general SELECT  statement with an editable WHERE clause only. By the help of EXISTS condition, WHERE statement’s content could include a new condition based on the other table and this approach provides developers to have two tables joined.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s