SQL excels at operating on dataset. In this post, I will discuss how to implement basic set operations in transact SQL (TSQL). The operations that I am going to discuss are union, intersection and complement (subtraction).
Implementing set operations using union, intersect and except
We can use TSQL keywords union, intersect and except to implement set operations. Since we are in an election year, I will use voter records of propositions as an example. We create the following table and insert 6 records into the table.
Voters 1, 2, 3 and 4 voted for proposition 30 and voters 4 and 5 voted for proposition 31.
The following TSQL statement implements union using the union keyword. The union returns voters who voted for either proposition 30 or 31.
The following TSQL statement implements intersection using the intersect keyword. The intersection will return voters who voted only for both proposition 30 and 31.
The following TSQL statement implements complement using the except keyword. The complement will return voters who voted for proposition 30 but not 31.
Implementing set operations using join
An alternative way to implement set operation in TSQL is to use full outer join, inner join and left outer join.
The following TSQL statement implements union using full outer join.
The following TSQL statement implements intersection using inner join.
The following TSQL statement implements complement using left outer join.
Which one to choose?
To choose which technique to use, just keep two things in mind:
- The union, intersect and except technique treats an entire record as a member.
- The join technique allows the member to be specified in the “on” clause. However, it is necessary to use Coalesce function to project sets on the two sides of the join into a single set.