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!

DISTINCT Clause in Oracle

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 demonstrates the use of DISTINCT clause in SQL which is used to restrict the duplicate rows in your SELECT Queries.

    1. Introduction

    A SELECT query displays all the data contained by the table, irrespective of quality and authenticity of the data. Data gets filtered only if restricting conditions are specified explicitly along with the SELECT query, using WHERE clause. The result set data may have duplicate records too. To eliminate the duplicate records, oracle provides DISTINCT clause to filter the duplicate records and show only one copy for the duplicate records.

    [​IMG]

    Syntax

    Code (Text):
    SELECT [DISTINCT] [COLUMN LIST]
    FROM [TABLE NAME]
    [WHERE] [CONDITIONS]

    Example 1: Select distinct departments from Employees table

    Code (SQL):
    SELECT DISTINCT DEPARTMENT_ID
    FROM EMPLOYEE

    DISTINCT DEP
    -----------------
      10
      20
      30

    Example 2: Select distinct Job id from Employees table

    Code (SQL):
    SELECT DISTINCT JOB_ID
    FROM EMPLOYEE

    DISTINCT JO
    -----------------
    DEV
    DDIR
    HR
    JMGR
    TEM
    TMGR
    SMGR
    2. DISTINCT clause for more than one field

    DISTINCT clause can be used with more than one field to get the unique combination of columns. Count of unique combinations of multiple columns would surely be more than the unique count of individual columns.

    Example 2.1. , Select distinct department and job from Employees table

    Code (SQL):
    SELECT DISTINCT DEPARTMENT_ID, JOB_ID
    FROM EMP

    3. UNIQUE clause

    UNIQUE clause also functions synonymously to DISTINCT. It eliminates duplicate records from the result set and shows only one copy of the duplicated record.
    Performance wise, both are same. Above examples can be re-executed using UNIQUE clause. Execution plan in the below example supports the point firmly.


    Code (SQL):
    SQL> SEELCT * FROM MYTAB;

             A
    ----------
            12
             3
             5
             1
             2
            34
            23

    7 ROWS selected.


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=91)
       1    0   TABLE ACCESS (FULL) OF 'MYTAB' (TABLE) (Cost=3 Card=7 BY
              tes=91)


    SQL> SELECT * FROM MYTAB;

             A
    ----------
             1
            34
             2
             5
            23
             3
            12

    7 ROWS selected.


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=7 Bytes=91)
       1    0   HASH (UNIQUE) (Cost=4 Card=7 Bytes=91)
       2    1     TABLE ACCESS (FULL) OF 'MYTAB' (TABLE) (Cost=3 Card=7
              Bytes=91)

    Example 3.1. Below SQL uses UNIQUE to get non duplicate combinations of Department id and Job id

    Code (SQL):
    SELECT UNIQUE DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEE      

    4. Restrictions of DISTINCTClause

    • DISTINCT degrades performance when working in large applications.
    • DISTINCT clause does not works with LOB columns

    Example 4.1. Demonstration of above restriction

    Code (SQL):
    SQL> SELECT DISTINCT APP_XML_INFO FROM MNS_R_PROJECT;
    SELECT DISTINCT APP_XML_INFO FROM MNS_R_PROJECT
                    *
    ERROR AT LINE 1:
    ORA-00932: INCONSISTENT DATATYPES: EXPECTED - GOT BLOB


    References
     

    Attached Files:

  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    What is the difference between Distinct and Unique.
    One difference is that Distinct cannot be used for Lobs.

    Any other differences ??
     
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Both DISTINCT and UNIQUE have no difference. As per Oracle documentation, UNIQUE is an alternative for DISTINCT.
    Both UNIQUE and DISTINCT doesn't works with LOBs; it is a restriction, not the difference