DB2 SQL SET OPERATIONS

Math’s Set theory directly inspires SQL SET OPERATIONS. SQL SET OPERATIONS combine result sets from two or more SQL Queries into a 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 has 4 SET OPERATORS.

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

CREATE TABLE QTEMP/TABLE2 (ID2 CHAR ( 10))
INSERT INTO QTEMP/TABLE2 VALUES('4'),('5'),('6'),('7'),('8'),('5'), ('6')

Table2

1. UNION

A union B

UNION SET operator returns a set of unique records from all SELECT queries involved in the operation. For UNION (or any Other SQL SET OPERATION) to work

1. The number of selected columns from all SELECT queries must match. Each query has one selected column, “id1” and “id2" in this given code.”
2. The data type of selected columns from one SELECT query must be either the same or convertible to selected columns of all other SELECT queries.
3. ORDER BY clause is only allowed after the last SELECT query.

SELECT id1 FROM table1 UNION SELECT id2 FROM table2

In the above code, there are 2 queries, “SELECT id1 FROM table1” and “SELECT id2 FROM table2,” attached with the UNION clause.

Here is how UNION going to work:

1. Execute Query 1 “SELECT id1 FROM table1” and get the result set.

SELECT id1 FROM table1

2. Execute Query 2 “SELECT id2 FROM table2” and get the result set.

SELECT id2 FROM table2

3. Merge both(all) result sets from the above steps.

4. Remove Duplicated values

The final result of Union

2. UNION ALL

If you don’t want to remove duplicate values, use UNION ALL.

SELECT id1 FROM table1 UNION ALL SELECT id2 FROM table2

The result will be a result set with all values, including all duplicate values.

The final result of Union ALL

3. EXCEPT

SQL EXCEPT returns records from the first SELECT query which are not present in any other SELECT queries. EXCEPT only return unique value, all duplicate values are removed from the final result.

SELECT id1 FROM table1 EXCEPT SELECT id2 FROM table2

Here is how SQL EXCEPT works

1. Execute Query 1 “SELECT id1 FROM table1” and get the result set.

2. Execute Query 2 “SELECT id2 FROM table2” and get the result set.

3. Get Query 1 records that are not in Query 2

4. Remove Duplicated values.

The final result of the EXCEPT

4. INTERSECT

A intersect B

SQL INTERSECT returns records which are common in all the SELECT queries involved in the operation. INTERSECT only return unique values, and all duplicate values are removed from the final result.
SELECT id1 FROM table1 INTERSECT SELECT id2 FROM table2

Here is how SQL INTERSECT works

1. Execute Query 1 “SELECT id1 FROM table1” and get the result set.

2. Execute Query 2 “SELECT id2 FROM table2” and get the result set.

3. Get common records from Query 1 and Query 2.

4. Remove Duplicated values