SQL UNION in DB2 for i SQL

SQL UNION in DB2 for i SQL

SQL Union operator

UNION keyword is used to combine results of two or more SELECT statement to form a full select. There are some prerequisite to use UNION keyword.

Every SELECT statement within the UNION must have the same number of columns.

The columns should have same datatype.

The columns in every SELECT should also be in the same order.

How SQL UNION works

First SQL process each SELECT statement in the UNION keyword to form an intermediate result table, then it combines the intermediate result table of each SELECT and deletes duplicate rows to form a combine result table.

UNION syntax

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

SQL Union Example

Example: Find Subject ids(only distinct) from both Student and Subject table

Refer the Structure, data of STUDENT and SUBJECT table from here.

SELECT subjectid FROM student 
UNION 
SELECT subjectid FROM subject

SUBJECTID
      201
      202
      203
      204
      205
      206
      207
      290

SQL Union ALL operator

UNION ALL keyword is used to combine results of two or more SELECT statement to form a fullselect. This is allows duplicate values in the final full0-select.

How SQL UNION ALL works

First SQL process each SELECT statement in the UNION ALL keyword to form an intermediate result table, then it combines the intermediate result table of each SELECT and allows duplicate rows to form a combine result table.

UNION ALL syntax

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

SQL Union ALL Example

Example: Find Subject ids(allow duplicate values also) from both Student and Subject table

Refer the Structure, data of STUDENT and SUBJECT table from here.

SELECT subjectid FROM student 
UNION ALL 
SELECT subjectid FROM subject

SUBJECTID
      201
      202
      203
      204
      290
      201
      202
      203
      204
      205
      206
      207