1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

EXISTS operator in SQL

Discussion in 'SQL PL/SQL' started by SBH, Oct 25, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article shows the use of EXISTS operator in SQL.

    Introduction

    EXISTS operator is used in WHERE conditions to test the existence of at least one matching record in subquery of the main query. EXISTS operator executes the sub query and returns Boolean output to notify the existence of the matching record. Please note that as soon as the first record is found in the sub query, the conditional statement is set to TRUE and aborted. Due to this reason, EXISTS are promoted to improve query performance.
    They are mainly used with correlated sub queries.

    Syntax:

    Code (Text):
    SELECT <COLUMN LIST>
    FROM TABLENAME
    WHERE EXISTS (SUB QUERY)
    Example 1: EMPLOYEE table contains an employee details. Table EMP_BLACKLIST contains the list of employee ids which are blacklisted. Below SQL query displays the details of Blacklisted employees.


    Code (SQL):
    SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID, E.SALARY
    FROM EMPLOYEE E
    WHERE EXISTS (SELECT 1
            FROM EMP_BLACKLIST
            WHERE EMPID = E. EMPLOYEE_ID)

    EXISTS can be used with NOT operator to negate the conditional output, if required. Output of the above query can be reversed by the addition of NOT operator. Then, it would return details of white collared employee.

    Example 2: Update Employee salary (increment by 1000) for those who are working in department 10, if the employee if not a blacklisted employee


    Code (SQL):
    UPDATE EMPLOYEE
    SET SALARY = SALARY + 1000
    WHERE DEPARTMENT_ID =10
    AND NOT EXISTS (SELECT 1
               FROM EMP_BLACKLIST
               WHERE EMPID = E.EMPLOYEE_ID)

    In terms of query performance, EXISTS are preferred over IN operator. This is because IN operator will check the matching of all the items in the list.


    For example, Query from Example 1 can be written as below:

    Code (SQL):
    SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID, E.SALARY
    FROM EMPLOYEE E
    WHERE EMPLOYEE_ID IN (SELECT EMPID FROM EMP_BLACKLIST)

    Above query will scan and take an employee id from EMP_BLACKLIST table and print the corresponding details from EMPLOYEE table. Using EXISTS, just checks the existence of an employee id in the EMP_BLACKLIST table, while printing the details. If existence condition is TRUE, then go ahead for display, else not.
    Now, difference is pretty clear that IN operates ‘From Inner to Outer query’, while EXISTS operates ‘From Outer to Inner query’.


    References