DB2 - SQL UNION Operator

Using UNION, multiple SELECT statements can be specified, and their results can be combined into a single result set.

A UNION must be comprised of two or more SELECT statements, each separated by the keyword UNION.

Each query in a UNION must contain the same columns, expressions or aggregate functions Column data types must be compatible

The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

Syntax: UNION

SELECT column_name(s) FROM table1
  UNION
SELECT column_name(s) FROM table2;

The UNION automatically removes any duplicate rows from the query result set.

If you want all occurrences of all matches returned, then you can use UNION ALL.

Syntax: UNION ALL

SELECT column_name(s) FROM table1
  UNION ALL
SELECT column_name(s) FROM table2;

DB2 Database:

Let's look at the "Orders" table data below:

Let's look at the "Customers" table data below:

Example 1:

The following SQL statement returns the cities (only distinct values) from both the "Orders" and the "Customers" table.

SELECT City FROM Orders
  UNION ALL
SELECT City FROM Customers
  ORDER BY City;

If "Orders" or "Customers" have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to include duplicate values.

Result:

If you want all occurrences of all matches returned, then you can use UNION ALL instead of UNION.

Example 2:

The following SQL statement returns the cities (with duplicates) from both the "Orders" and the "Customers" table.

SELECT City FROM Orders
  UNION
SELECT City FROM Customers
  ORDER BY City;

If "Orders" or "Customers" have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!