Combining result tables from multiple SELECT statements

When you combine the results of multiple SELECT statements, you can choose what to include in the result table. You can include all rows, only rows that are in the result table of both SELECT statements, or only rows that are unique to the result table of the first SELECT statement.

About this task

Assume that you want to combine the results of two SELECT statements that return the following result tables:

Example: R1 result table
COL1	 COL2
a	    a 
a	    b 
a	    c
Example: R2 result table
COL1	 COL2 
a	    b 
a	    c 
a	    d

You can use the set operators to combine two or more SELECT statements to form a single result table:

UNION
UNION returns all of the values from the result table of each SELECT statement. If you want all duplicate rows to be repeated in the result table, specify UNION ALL. If you want redundant duplicate rows to be eliminated from the result table, specify UNION or UNION DISTINCT.

For example, the following example is the result of specifying UNION for R1 and R2.

COL1	 COL2 
a	    a 
a	    b 
a	    c 
a	    d
EXCEPT
Returns all rows from the first result table (R1) that are not also in the second result table (R2). If you want all duplicate rows from R1 to be contained in the result table, specify EXCEPT ALL. If you want redundant duplicate rows in R1 to be eliminated from the result table, specify EXCEPT or EXCEPT DISTINCT.

The result of the EXCEPT operation depends on the which SELECT statement is included before the EXCEPT keyword in the SQL statement. For example, if the SELECT statement that returns the R1 result table is listed first, the result is a single row:

COL1	 COL2 
a	    a

If the SELECT statement that returns the R2 result table is listed first, the final result is a different row:

COL1	 COL2 
a	    d
INTERSECT
Returns rows that are in the result table of both SELECT statements. If you want all duplicate rows to be contained in the result table, specify INTERSECT ALL. If you want redundant duplicate rows to be eliminated from the result table, specify INTERSECT or INTERSECT DISTINCT.

For example, the following example is the result of specifying UNION for R1 and R2.

COL1	 COL2 
a	    b  
a	    c

When you specify one of the set operators, Db2 processes each SELECT statement to form an interim result table, and then combines the interim result table of each statement. If the nth column of the first result table (R1) and the nth column of the second result table (R2) have the same result column name, the nth column of the result table has that same result column name. If the nth column of R1 and the nth column of R2 do not have the same names, the result column is unnamed.

Procedure

  • To combine two or more SELECT statements to form a single result table, use the set operators: UNION, EXCEPT or INTERSECT.
    For example, assume that you have the following tables to manage stock at two book stores.
    Table 1. STOCKA
    ISBN TITLE AUTHOR NOBEL PRIZE
    8778997709 For Whom the Bell Tolls Hemmingway N
    4599877699 The Good Earth Buck Y
    9228736278 A Tale of Two Cities Dickens N
    1002387872 Beloved Morrison Y
    4599877699 The Good Earth Buck Y
    0087873532 The Labyrinth of Solitude Paz Y
    Table 2. STOCKB
    ISBN TITLE AUTHOR NOBEL PRIZE
    6689038367 The Grapes of Wrath Steinbeck Y
    2909788445 The Silent Cry Oe Y
    1182983745 Light in August Faulkner Y
    9228736278 A Tale of Two Cities Dickens N
    1002387872 Beloved Morrison Y
    Example: UNION clause
    Suppose that you want a list of books whose authors have won the Nobel Prize and that are in stock at either store. The following SQL statement returns these books in order by author name without redundant duplicate rows:
    SELECT TITLE, AUTHOR
  FROM STOCKA
  WHERE NOBELPRIZE = 'Y'
UNION
SELECT TITLE, AUTHOR
  FROM STOCKB
  WHERE NOBELPRIZE = 'Y'
 ORDER BY AUTHOR

    This statement returns the following final result table:

    Table 3. Result of UNION
    TITLE AUTHOR
    The Good Earth Buck
    Light in August Faulkner
    Beloved Morrison
    The Silent Cry Oe
    The Labyrinth of Solitude Paz
    The Grapes of Wrath Steinbeck
    Example: EXCEPT clause
    Suppose that you want a list of books that are only in STOCKA. The following SQL statement returns the book names that are in STOCKA only without any redundant duplicate rows:
    SELECT TITLE
  FROM STOCKA
EXCEPT
SELECT TITLE
  FROM STOCKB
  ORDER BY TITLE;

    This statement returns the following result table:

    Table 4. Result of EXCEPT
    TITLE
    For Whom the Bell Tolls
    The Good Earth
    The Labyrinth of Solitude
    Example: INTERSECT clause

    Suppose that you want a list of books that are in both STOCKA and in STOCKB. The following statement returns a list of all books from both of these tables with redundant duplicate rows are removed.

    SELECT TITLE
  FROM STOCKA
INTERSECT
SELECT TITLE
  FROM STOCKB
  ORDER BY TITLE;
    This statement returns the following result table:
    Table 5. Result of INTERSECT
    TITLE
    A Tale of Two Cities
    Beloved
  • To keep all duplicate rows when combining result tables, specify the ALL keyword with the set operator clause.

    The following examples use the STOCKA and STOCK B tables from the previous step.

    Example: UNION ALL
    The following SQL statement returns a list of books that won Nobel prizes and are in stock at either store, with duplicates included.
    SELECT TITLE, AUTHOR
  FROM STOCKA
  WHERE NOBELPRIZE = 'Y'
UNION ALL
SELECT TITLE, AUTHOR
  FROM STOCKB
  WHERE NOBELPRIZE = 'Y'
 ORDER BY AUTHOR

    This statement returns the following result table:

    Table 6. Result of UNION ALL
    TITLE AUTHOR
    The Good Earth Buck
    The Good Earth Buck
    Light in August Faulkner
    Beloved Morrison
    Beloved Morrison
    The Silent Cry Oe
    The Labyrinth of Solitude Paz
    The Grapes of Wrath Steinbeck
    Example: EXCEPT ALL
    Suppose that you want a list of books that are only in STOCKA. The following SQL statement returns the book names that are in STOCKA only with all duplicate rows: 
    SELECT TITLE
  FROM STOCKA
EXCEPT ALL
SELECT TITLE
  FROM STOCKB
  ORDER BY TITLE;

    This statement returns the following result table:

    Table 7. Result of EXCEPT ALL
    TITLE
    For Whom the Bell Tolls
    The Good Earth
    The Good Earth
    The Labyrinth of Solitude
    Example: INTERSECT ALL
    Suppose that you want a list of books that are in both STOCKA and in STOCKB, including any duplicate matches. The following statement returns a list of titles that are in both stocks, including duplicate matches. In this case, one match exists for "A Tale of Two Cities" and one match exists for "Beloved." 
    SELECT TITLE
  FROM STOCKA
INTERSECT ALL
SELECT TITLE
  FROM STOCKB
  ORDER BY TITLE;

    This statement returns the following result table:

    Table 8. Result of INTERSECT ALL
    TITLE
    A Tale of Two Cities
    Beloved
  • To eliminate redundant duplicate rows when combining result tables, specify one of the following keywords:
    • UNION or UNION DISTINCT
    • EXCEPT or EXCEPT DISTINCT
    • INTERSECT or INTERSECT DISTINCT
  • To order the entire result table, specify the ORDER BY clause at the end.