EXPECT ALL <\/code>operators. I\u2019ll explain all standard variants, and for the missing ones in T-SQL, I\u2019ll provide workarounds.<\/p>\nYou apply a set operator to two input table expressions, which I\u2019ll refer to as TE1 and TE2:<\/p>\n
TE1\r\n<set operator> \r\nTE2<\/pre>\nThe above represents a table expression.<\/p>\n
A statement based on a table expression with a set operator can have an optional ORDER BY<\/code> clause applied to the result, using the following syntax:<\/p>\nTE1 <set operator> TE2 \r\n[ORDER BY <order by list>];<\/pre>\nYou\u2019re probably familiar with the set operators that T-SQL supports. Still, let me briefly explain what each operator does:<\/p>\n
\nUNION<\/code>: Returns distinct rows that appear in TE1<\/code>, TE2<\/code> or both. That is, if row R<\/code> appears in TE1<\/code>, TE2<\/code> or both, irrespective of number of occurrences, it appears exactly once in the result.<\/li>\nUNION ALL<\/code>: Returns all rows that appear in TE1<\/code>, TE2<\/code> or both. That is, if row R appears m times in TE1<\/code> and n times in TE2<\/code>, it appears m + n<\/code> times in the result.<\/li>\nINTERSECT<\/code>: Returns distinct rows that are common to both TE1<\/code> and TE2<\/code>. That is, if row R<\/code> appears at least once in TE1<\/code>, and at least once in TE2<\/code>, it appears exactly once in the result.<\/li>\nINTERSECT ALL<\/code>: Returns all rows that are common to both TE1<\/code> and TE2<\/code>. That is, if row R appears m times in TE1<\/code> and n times in TE2<\/code>, it appears minimum(m, n)<\/code> times in the result. For example, if R<\/code> appears 5 times in TE1<\/code> and 3 times in TE2<\/code>, it appears 3 times in the result.<\/li>\nEXCEPT<\/code>: Returns distinct rows that appear in TE1<\/code> but not in TE2<\/code>. That is, if a row R<\/code> appears in TE1<\/code>, irrespective of the number of occurrences, and does not appear in TE2<\/code>, it appears exactly once in the output.<\/li>\nEXCEPT ALL<\/code>: Returns all rows that appear in TE1<\/code> but don\u2019t have an occurrence match in TE2<\/code>. That is, if a row R<\/code> appears m times in TE1<\/code>, and n times in TE2<\/code>, it appears maximum((m - n), 0)<\/code> times in the result. For example, if R<\/code> appears 5 times in TE1<\/code> and 3 times in TE2<\/code>, it appears 2 times in the result. If R<\/code> appears 3 times in TE1<\/code> and 5 times in TE2<\/code>, it doesn\u2019t appear in the result.<\/li>\n<\/ul>\nAn interesting question is why would you use a set operator to handle a given task as opposed to alternative tools to combine data from multiple tables, such as joins and subqueries? To me, one of the main benefits is the fact that when set operators compare rows, they implicitly use distinctness-based comparison and not equality-based comparison. Recall that distinctness-based comparison handles NULL<\/code>s and non-NULL<\/code> values the same way, essentially using two-valued logic instead of three-valued logic. That\u2019s often the desired behavior, and with set operators it simplifies the code a great deal. For example, the following code identifies distinct locations that are both customer locations and employee locations:<\/p>\nSELECT country, region, city FROM Sales.Customers\r\nINTERSECT\r\nSELECT country, region, city FROM HR.Employees;<\/pre>\nThis code generates the following output:<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-phone-description-automatically-2.png\")
<\/p>\n
Since set operators implicitly use the distinct predicate to compare rows (not to confuse with the fact that without an explicit quantifier they use the DISTINCT<\/code> quantifier by default), you didn\u2019t need to do anything special to get a match when comparing two NULL<\/code>s and a nonmatch when comparing a NULL<\/code> with a non-NULL<\/code> value. The location UK, NULL<\/code>, London is part of the result since it appears in both inputs.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-45.png\")
<\/p>\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-blue-rectangle-with-black-text-description-auto.png\")
<\/p>\n