10 Top SQL Set Operator Rules

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.

Top 10 Rules of SET Operators

  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

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

Recent Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.