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 in SELECT query

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 explains the use of UNION operator in SELECT query to combine the results of more than one SELECT Queries.

    Introduction

    UNION is one of the SET operators in Oracle. It unites the result set of two SELECT queries, eliminates duplicates, sorts the result set (in ascending order) and displays the result set.

    [​IMG]

    Syntax:

    Code (Text):
    SELECT QUERY1
    UNION
    SELECT QUERY2
    ….
    SELECT QUERY N
    For example 1

    Below table shows the data from EMPLOYEE table. Employee in a department possesses a Job Id.

    [​IMG]

    Below query finds the union of JOB ID from Department Id 10 and 20.

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

    JOB_ID
    -----------
    ADM
    DEV
    HR
    MGR
    TECH
    Please note in the above result set, it is distinct and sorted in ascending alphabetic order.

    2. Usage Notes

    • The SELECT query must select columns of same data type family with respect to position. System raises an error message upon violation of the rule as shown below.

      Code (SQL):
      SELECT 2892920 FROM DUAL
      UNION
      SELECT 'CLUB-ORACLE' FROM DUAL;

      SELECT 2892920 FROM DUAL
             *
      ERROR at line 1:
      ORA-01790: expression must have same datatype AS corresponding expression
    • Ordering of the combined result set is not possible through specifying the column in ORDER BY clause. Instead, positional ordering is done to sort the result set. ORDER BY can appear once at the end of the query. For example,

      Code (SQL):
      SELECT EMPLOYEE_ID, JOB_ID, SALARY
      FROM EMPLOYEE
      UNION
      SELECT EMPLOYEE_ID, JOB_ID, SALARY
      FROM JOB_HISTORY
      ORDER BY 3
    • The LONG, BLOB, CLOB, BFILE, VARRAY, or nested table are not permitted for use in Set operators. For update clause is not allowed with the set operators.


    References
     

    Attached Files:

  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    What if
    1. my query 1 contains 2 fields and query 2 contains 3 fields??

    2. will it won't works for different datatypes ??

    e.g query1 contains 1 field and is of varchar2 type
    and query2 contains 1 field and is of number type??
    i.e. mismatched datatype ??
     
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Kiran,
    For UNION queries, number of columns in all the participating queries must be same. And their data types must belong to same family.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Case 1: Mismatched datatype:

    SELECT TO_CHAR(2892920) FROM DUAL
    UNION
    SELECT 'CLUB-ORACLE' FROM DUAL;

    Case 2: Mismatched number of fields:

    SELECT EMPLOYEE_ID, JOB_ID, SALARY
    FROM EMPLOYEE
    UNION
    SELECT EMPLOYEE_ID, JOB_ID, 0
    FROM JOB_HISTORY;

    If SALARY field is varchar2 , then use '0', a char value.
    If you are not sure about the datatype, then try using NULL instead of 0.

    Thanks
    Kiran Marla
     
  5. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Yes, substitution of missing columns can be doe using NULL and 0, but that ends up again in following the concept of 'Same count of columns of same data type'
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Yes, ultimately query ends up with same number of columns. but we need not take same number of columns from the table , with sub variable.