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!

UNION ALL in SELECT query

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 of more than one SELECT statements into one result set in a logical manner. This article explains the usage of UNION ALL operation in SELECT statements.

    2. Introduction

    UNION ALL is a set operator which simply unites the result sets of two SELECT queries, without eliminating duplicates from the combined result set. In addition, it doesn’t sorts the final result set in any order. It can be understood as dumping of multiple result sets into one, in the same order as that of SELECT statements in the UNION ALL query.

    [​IMG]

    3. Rules for writing UNION ALL query remains the same as below:

    1. Count of selected columns in all the participating queries must be same.
    2. Data types of the column must be in sync by column position.

    Examples 1. Below query lists the all the JOB IDs in Department 10 and 20


    Code (SQL):


    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    UNION ALL
    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 20

    JOB
    ----------
    MGR
    TECH
    HR
    MGR
    HR

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=40)
       1    0   UNION-ALL
       2    1     TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=2
               Bytes=20)

       3    1     TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=2
               Bytes=20)



     
    One can compare the working of UNION set operator in this case.

    We shall rewrite the above example using UNION

    Code (SQL):


    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    UNION
    SELECT JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 20


    JOB
    ----------
    HR
    MGR
    TECH


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=40)
       1    0   SORT (UNIQUE) (Cost=8 Card=4 Bytes=40)
       2    1     UNION-ALL
       3    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
              =2 Bytes=20)

       4    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
              =2 Bytes=20)



     
    If we compare the EXPLAIN PLAN of both the result sets, it is noticed that UNION has an extra overhead of sorting the final result set, which is not applicable for UNION ALL. This implies that UNION ALL gives better performance as compared to UNION.


    4. Ordering in UNION ALL queries

    Ordering of the UNION ALL query result set can be achieved using positional ordering mechanism. Position number of the column can be specified in the ORDER BY clause to sort the result based on a column of the SELECT query.

    Example 4.1. Below query shows the ordering of the result set by DEPARTMENT_ID column

    Code (SQL):

    SELECT DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    UNION ALL
    SELECT DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 20
    ORDER BY 1

          DEP  JOB_ID
    ---------- ----------
            10 TECH
            10 MGR
            20 MGR
            20 HR
            20 HR


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=40)
       1    0   SORT (ORDER BY) (Cost=6 Card=4 Bytes=40)
       2    1     UNION-ALL
       3    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
              =2 Bytes=20)

       4    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card
              =2 Bytes=20)

     
     

    Attached Files: