SQL SET OPERATIONS

DB2 SQL SET OPERATIONS : BASIC


SQL SET OPERATIONS are directly inspired from Math’s Set theory. SQL SET OPERATIONS combine result sets from two or more SQL Queries into single result set.

For SQL SET OPERATIONS

  1. The two queries must contain the same number of columns.
  2. Each column of the first query must be either the same data type as the corresponding column of the second query or convertible to the same data type as the corresponding column of the second query

SQL have 4 SET OPERATORS.

Operator Result set
UNION (Unique records from all queries)
[No Duplicate data]
UNION ALL (All records from all queries)
[May Contain Duplicate data]
EXCEPT (All records from First query which are NOT duplicate in any other query)
[No Duplicate data]
INTERSECT (Common records from all queries)
[No Duplicate data]

Sample Data to Understand SQL SET OPERATIONS

CREATE TABLE QTEMP/TABLE1 (ID1 CHAR ( 10))
INSERT INTO QTEMP/TABLE1 VALUES('1'),('2'),('3'),('4') ,('5'),('1'), ('2')

TABLE1.ID1
1
  • Values ‘1’ and ‘2’ are duplicate with in same table.
  • Values ‘4’ and ‘5’ are duplicate with TABLE2.
  • Value ‘3’ is not duplicate at all .
2
3
4
5
1
2

CREATE TABLE QTEMP/TABLE2 (ID2 CHAR ( 10))
INSERT INTO QTEMP/TABLE2 VALUES('4'),('5'),('6'),('7'),('8'),('5'), ('6')
TABLE2.ID2
4
  • Values ‘5’ and ‘6’ are duplicate with in same table.
  • Values ‘4’ and ‘5’ are duplicate with TABLE1.
  • Value ‘8’ is not duplicate at all .
5
6
7
8
5
6

IBM i developer.

View Comments
There are currently no comments.