I am going to promote my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.
I will exploring the Set Operators today. A Venn diagram is used to express different operations between sets. The above image represents the four possible operations given two sets, A and B, and three operators. We will get into the operators supported by Microsoft Transaction SQL shortly.
To demonstrate these operators, we need a two temporary tables to query against. The TSQL code below will create temporary tables A and B.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- -- ~ Setup Play Table ~ -- -- Drop existing table [A] IF EXISTS ( SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#A%' ) DROP TABLE #A; GO -- Make temp table [A] CREATE TABLE #A (NUM INT PRIMARY KEY); GO -- Add data to table [A] INSERT INTO #A VALUES (1), (2), (3), (4), (5), (6); GO -- Drop existing table [B] IF EXISTS ( SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#B%' ) DROP TABLE #B; GO -- Make temp table [B] CREATE TABLE #B (NUM INT PRIMARY KEY); GO -- Add data to table [B] INSERT INTO #B VALUES (5), (6), (7), (8), (9), (10); GO |
The UNION operator combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. By default, the operator removes any duplicates. Using set notation, the A ∪ B notation is the union of both data sets in the above diagram.
To use this operator, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.
|
1 2 3 4 |
-- ~ UNION ~ no duplicates SELECT * FROM #A UNION SELECT * FROM #B |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
NUM ----------- 1 2 3 4 5 6 7 8 9 10 |
Using the ALL clause of the UNION operator shows the all rows including the duplicates. Please note that the order of the rows in the result set is not guaranteed. In our example, both values 5 and 6 are duplicated in the output list.
|
1 2 3 4 5 |
-- ~ UNION ALL ~ show duplicates SELECT * FROM #A UNION ALL SELECT * FROM #B </span> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
NUM ----------- 1 2 3 4 5 6 5 6 7 8 9 10 |
The EXCEPT operator returns any distinct values from the left query that are not also found on the right query. Using set notation, A – B is the EXCEPT operation of set A in regards to B. Again, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.
|
1 2 3 4 5 |
-- ~ EXCEPT ~ ones that show just in A SELECT * FROM #A EXCEPT SELECT * FROM #B </span> |
|
1 2 3 4 5 6 |
NUM ----------- 1 2 3 4 |
Since this operator is order specific, the set notion B – A is the EXCEPT operation of set B in regards to A. Please see the image above for a visual representation using set theory.
|
1 2 3 4 |
-- ~ EXCEPT ~ ones that show just in B SELECT * FROM #B EXCEPT SELECT * FROM #A |
|
1 2 3 4 5 6 |
NUM ----------- 7 8 9 10 |
Last but not least, the INTERSECT operator returns any distinct values in common from both the left and right queries.
Using set notation, A ∩ B is the INTERSECT operation between sets A and B. As usual, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.
|
1 2 3 4 |
-- ~ INTERSECT ~ ones that show in both A & B SELECT * FROM #A INTERSECT SELECT * FROM #B |
|
1 2 3 4 |
NUM ----------- 5 6 |
To wrap-up this article, set operators are a great way to combine two similar query outputs using the UNION, EXCEPTION or INTERSECTION as combinatorial rules. Next time, I will be researching the String Operators.
