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!

INTERSECT set operator in Oracle

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

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    The article explains the third SET operator i.e. INTERSECTION. As the name suggests, it produces the intersected result of the component queries.


    2. Introduction

    As the word suggests, INTERSECT set operator returns the common records from the participating SELECT queries. It combines the result set of all the SELECT statements, and then picks the common records from the result set of all the participating SELECT queries and displays in the final result set.
    [​IMG]
    3. Notes

    1. Number of selected columns must be same in all the participating SELECT queries
    2. Intersection of result set is based on common combination of all the selected columns. For example,


      Code (SQL):
      SELECT DEPARTMENT_ID, JOB_ID
      FROM EMPLOYEE
      WHERE EMPLOYEE_ID IN (100,120,140)
      INTERSECT
      SELECT DEPARTMENT_ID, JOB_ID
      FROM EMPLOYEE
      WHERE EMPLOYEE_ID IN (110,130,150)

            DEPT JOB
      ---------- ----------
              10 MGR
              20 HR
    3. It eliminates duplicates from the final result set. Also it sorts the result in ascending order. The common record selection and sorting is the extra overhead in INTERSECT queries, which degrades the performance.
    4. Data types of the selected columns must be compatible in all SELECT queries, by position.
    5. Positional ordering should be used to order the final result set.
    6. It does not works with columns of type LONG, BLOB, CLOB, BFILE, VARRAY, or nested table. Also it cannot be used in the queries using TABLE collection expressions.
    7. FOR UPDATE clause cannot be specified in the queries using SET operators.


    Examples,

    1. Below query intersects the result set of two SELECTs to find the common JOB IDs in department 20 and 30

    Code (SQL):
    SELECT JOB FROM EMPLOYEE
    WHERE DEPT = 30
    INTERSECT
    SELECT JOB FROM EMPLOYEE
    WHERE DEPT = 20
    2. Below query compares two tables MYTAB and YOURTAB, by making use of UNION and INTERSECT

    Code (SQL):
    (SELECT JOB FROM EMPLOYEE
    MINUS
    SELECT JOB FROM EMP_ARCH
    )
    UNION ALL
    (SELECT JOB FROM EMP_ARCH
    MINUS
    SELECT JOB FROM EMPLOYEE
    )

    JOB
    ----------
    ADM
    DEV
     

    Attached Files: