Understanding SQL Set Operators: UNION, INTERSECT, MINUS

Set operators useful when you want to read or filter data of multiple tables. You can’t apply set operators on a single table.

The popular set operators are INTERSECT, UNION and MINUS. Set operators useful for data analytics projects.

Set operator rules

1. Each query will have at least two SELECT Statements separated by a SET Operator
2. SET Operators are UNION, INTERSECT, or EXCEPT/MINUS
3. Must specify the same number of columns from the same domain (data type/range)
4. If using Aggregates, both SELECTs much have their own GROUP BY
5. Both SELECTS must have a FROM Clause
6. The First SELECT is used for all ALIAS and FORMAT Statements
7. The Second SELECT will have the ORDER BY statement which must be a number
8. When multiple operators the order of precedence is INTERSECT, UNION, and MINUS
9. Parentheses can change the order of Precedence
10. Duplicate rows are eliminated in the spool unless the ALL keyword is used

SQL Examples for SET Operators

Here are set operators in sql with examples.

1. UNION Example

SELECT * FROM MY_TABLE
UNION
SELECT * FROM SRINIMF_TAB;

The results of this query is it pulls all the unique records. You need to use UNION ALL to get duplicate records.

2. Intersect Example

SELECT * FROM MY_TABLE 
INTERSECT
 SELECT * FROM SRINIMF_TAB; 

The result of this query is only matching rows you will get.

3. Minus Example

SELECT * FROM MY_TABLE
MINUS
SELECT * FROM SRINIMF_TAB; 

The result of this query is it excludes matching rows, and pulls remaining rows.

References

• Tera-Tom Genius Series – DB2 SQL