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. <\/p>\n
I will exploring the Set Operators<\/a> today. A Venn diagram<\/a> 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. <\/p>\n To demonstrate these operators, we need a two temporary tables to query against. The TSQL code below will create temporary tables A and B.<\/p>\n The UNION<\/a> 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<\/span> notation is the union of both data sets in the above diagram.<\/p>\n 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.<\/p>\n Using the ALL<\/a> 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.<\/p>\n The EXCEPT<\/a> operator returns any distinct values from the left query that are not also found on the right query. Using set notation, A – B<\/span> 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.<\/p>\n Since this operator is order specific, the set notion B – A<\/span> is the EXCEPT operation of set B in regards to A. Please see the image above for a visual representation using set theory.<\/p>\n\r\n--\r\n-- ~ Setup Play Table ~\r\n--\r\n\r\n-- Drop existing table [A]\r\nIF EXISTS \r\n(\r\n SELECT * \r\n FROM tempdb.sys.objects T \r\n WHERE T.TYPE = 'U' AND T.name LIKE '#A%'\r\n) \r\nDROP TABLE #A;\r\nGO\r\n\r\n-- Make temp table [A]\r\nCREATE TABLE #A (NUM INT PRIMARY KEY);\r\nGO\r\n\r\n-- Add data to table [A]\r\nINSERT INTO #A VALUES (1), (2), (3), (4), (5), (6); \r\nGO\r\n\r\n-- Drop existing table [B]\r\nIF EXISTS \r\n(\r\n SELECT * \r\n FROM tempdb.sys.objects T \r\n WHERE T.TYPE = 'U' AND T.name LIKE '#B%'\r\n) \r\nDROP TABLE #B;\r\nGO\r\n\r\n-- Make temp table [B]\r\nCREATE TABLE #B (NUM INT PRIMARY KEY);\r\nGO\r\n\r\n-- Add data to table [B]\r\nINSERT INTO #B VALUES (5), (6), (7), (8), (9), (10); \r\nGO\r\n\r\n<\/pre>\n<\/p>\n
\r\n-- ~ UNION ~ no duplicates\r\nSELECT * FROM #A\r\nUNION\r\nSELECT * FROM #B\r\n<\/pre>\n<\/p>\n
\r\nNUM\r\n-----------\r\n1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\n<\/p>\n
\r\n-- ~ UNION ALL ~ show duplicates\r\nSELECT * FROM #A\r\nUNION ALL\r\nSELECT * FROM #B\r\n<\/span><\/pre>\n<\/p>\n
\r\nNUM\r\n-----------\r\n1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\n<\/p>\n
\r\n-- ~ EXCEPT ~ ones that show just in A\r\nSELECT * FROM #A\r\nEXCEPT \r\nSELECT * FROM #B\r\n<\/span><\/pre>\n<\/p>\n
\r\nNUM\r\n-----------\r\n1\r\n2\r\n3\r\n4\r\n<\/pre>\n<\/p>\n
\r\n-- ~ EXCEPT ~ ones that show just in B\r\nSELECT * FROM #B\r\nEXCEPT \r\nSELECT * FROM #A\r\n<\/pre>\n<\/p>\n
\r\nNUM\r\n-----------\r\n7\r\n8\r\n9\r\n10\r\n<\/pre>\n<\/p>\n