TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

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:

OrderidCustomernumberOrderdateCity
1001102020-09-08London
1002992020-09-01Newyork
1003172020-08-25Paris
1004762020-09-19Dubai
1005442020-09-25Sydney

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

CustomernumberCustomernameCountryCity
76JackAmericaNewyork
17JancyGermanyCalifornia
20CarmenRussiaLondon
10RobertIndiaNewdelhi
99BrianChinaChennai


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:

City
California
Chennai
Dubai
London
Newdelhi
Newyork
Paris
Sydney

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:

City
California
Chennai
Dubai
London
London
Newdelhi
Newyork
Newyork
Paris
Sydney


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

Are you looking for Job Change? Job Portal