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!

MINUS 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

    Oracle provides SET operators to combine the result set of multiple queries. A SQL query using SET operators to combine multiple query is known as Compound Query. In this article, we shall discuss on working of MINUS set operator.

    2. Introduction

    MINUS is a SQL set operator which is used to get the overhead records in the first component SELECT query. It returns the unique records contained by the first query but missing in the second one.
    [​IMG]

    3. Usage Rules

    The normal rules for the usage of SET operators are applicable to MINUS also. The number of expressions, columns in each of the participating query must match, along with their data types. However, they may differ in their names as the component queries can query different tables. Please note that in case of SET operators, oracle does not perform implicit conversion, so the selected column or expression types must belong to the same data type family. For instance, CHAR and VARCHAR2 fall under same family, while NUMBER, INTEGER, PLS_INTEGER, SIMPLE_INTEGER belong to the same family.

    4. Restrictions

    As per the restrictions on MINUS operator, it cannot be used with columns or expressions of LONG, BLOB, CLOB, BFILE, VARRAY, Nested table or TABLE collection expressions. FOR UPDATE clause cannot be associated in SET operator queries.
    Notes
    1. MINUS operator removes the duplicates from the final result set.
    2. Similar to other SET operators like UNION and INTERSECT, it sorts the result set in ascending order.

    Examples
    1. Below query returns the JOB IDs from Department 10, which are not yet finalized in Department 20


    Code (SQL):
    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    MINUS
    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 20

    JOB_ID
    --------
    ADM
    DEV

    2. Below query returns the locations which is not yet assigned to any department

    Code (SQL):
    SELECT LOCATION_ID
    FROM LOCATIONS
    MINUS
    SELECT LOCATION_ID
    FROM DEPARTMENT

    LOCATIOS
    --------------
        180
        190
        200
     

    Attached Files: