SQL SET OPERATIONS

DB2 SQL SET OPERATIONS : UNION and UNION ALL


  1. SQL UNION

    UNION SET operator return unique record from all SELECT queries involved in operation.For UNION (or any Other SQL SET OPERATION) to work

    1. Number of selected columns from all SELECT queries must match. In this given code each query have one selected columns “id1” and “id2”.
    2. Data type of selected columns from one SELECT query must be either same or convertible to selected columns of all other SELECT queries
    3. ORDER BY clause is only allowed after last SELECT query.

    SELECT id1 FROM table1 UNION SELECT id2 FROM table2

    In above code there are 2 queries “SELECT id1 FROM table1” and “SELECT id2 FROM table2” attached with UNION clause

    Here is how UNION going to work [SAMPLE DATA]

    1. Execute Query 1
    “SELECT id1 FROM table1”
    and get result set.

    TABLE1.ID1
    1
    2
    3
    4
    5
    1
    2
    2. Execute Query 2
    “SELECT id2 FROM table2”
    and get result set.

    TABLE2.ID2
    4
    5
    6
    7
    8
    5
    6
    3. Merge all
    result sets from above steps.
     

    ID1
    1
    2
    3
    4
    5
    1
    2
    4
    5
    6
    7
    8
    5
    6
    4. Remove Duplicated values.
     
     

    ID1
    1
    2
    3
    4
    5
    6
    7
    8
  2. SQL UNION ALL

    If you don’t want to remove duplicate values means if you want all records from all queries use UNION ALL.

    SELECT id1 FROM table1 UNION ALL SELECT id2 FROM table2

    Result will be a result set with all values including duplicate.

    ID1
    1
    2
    3
    4
    5
    1
    2
    4
    5
    6
    7
    8
    5
    6

IBM i developer.

View Comments
There are currently no comments.