SQL Set Operators - Combining Queries Tutorial

Understanding SQL Set Operators

SQL set operators work on the result sets of two or more SELECT statements. They treat these results as sets and perform operations like union, intersection, or difference.

The three main set operators are UNION, INTERSECT, and EXCEPT. Each serves a different purpose in combining query results.

• UNION combines distinct rows from both queries.
• INTERSECT returns rows common to both queries.
• EXCEPT returns rows from the first query that are not in the second.

Using UNION and UNION ALL

The UNION operator merges the results of two queries and removes duplicate rows.

If you want to include duplicates, use UNION ALL, which is faster because it skips the duplicate elimination step.

• Both queries must have the same number of columns.
• Corresponding columns must have compatible data types.
• Column names in the result set come from the first query.

Using INTERSECT

INTERSECT returns only the rows that appear in both query results.

This operator is useful when you want to find common records between two datasets.

• Both queries must have the same number of columns with compatible types.
• Duplicates are removed in the result.

Using EXCEPT

EXCEPT returns rows from the first query that do not appear in the second query.

It is useful for finding differences between datasets.

• Both queries must have the same number of columns with compatible types.
• Duplicates are removed in the result.

Important Rules and Considerations

When using set operators, keep in mind these important rules:

• All SELECT statements must have the same number of columns.
• Corresponding columns must have compatible data types.
• Column names in the final result come from the first SELECT statement.
• ORDER BY can only be applied once at the end of the combined query.
• Use parentheses to control the order of operations when combining multiple set operators.