Day 9: Set operations

A. Lesson overview

Activity breakdown

Business scenario. A new batch of web and in-store sign-ups has arrived in Staging.CustomerImport, and Merchandising has submitted price changes in Staging.ProductPriceUpdate. Before anything is loaded, the data team must answer: Which import emails are new? Which belong to existing customers, and have their details changed? Which prices would actually change? Comparing whole sets of rows is what set operators do best.

B. Theory explained clearly

Part 1 — Rows as sets

A query result is a set of rows. Set operators combine two complete queries vertically (rows on top of rows), whereas joins combine tables horizontally (columns side by side).

EXCEPT is not symmetric: A EXCEPT B and B EXCEPT A are different questions. SQL Server does not support INTERSECT ALL or EXCEPT ALL.

USE RetailLab;
GO

SELECT City FROM Sales.Customer
UNION
SELECT City FROM Sales.Store
ORDER BY City;        -- 32 distinct cities

UNION versus UNION ALL

UNION must find and remove duplicates, which means sorting or hashing every row. UNION ALL simply appends. If the two inputs cannot overlap (for example, different years) or you want every row (an activity feed), use UNION ALL: it is cheaper and states your intent. A common anti-pattern is writing UNION by habit and silently losing legitimate duplicate rows — two orders of the same amount on the same day, for instance.

Part 2 — Rules

Collation. Text comparisons in UNION, INTERSECT and EXCEPT use the column collation. In RetailLab (case-insensitive), 'BEN.CARTER@EXAMPLE.COM' and 'ben.carter@example.com' are the same row. On a case-sensitive database they would be different. Trailing spaces are ignored in comparisons; leading spaces are not — use TRIM.

Precedence. INTERSECT is evaluated before UNION and EXCEPT; UNION and EXCEPT have equal precedence and run left to right.

SELECT n FROM (VALUES (1), (2)) AS A(n)
UNION
SELECT n FROM (VALUES (2), (3)) AS B(n)
INTERSECT
SELECT n FROM (VALUES (3), (4)) AS C(n);     -- B INTERSECT C = {3}; A UNION {3} = {1, 2, 3}

Wrap the first two in parentheses — (SELECT … UNION SELECT …) INTERSECT SELECT … — and the result is {3}. As with AND/OR, add parentheses whenever you mix operators.

Part 3 — NULLs and duplicates

Set operators compare rows for distinctness, and two NULLs are considered not distinct — the same. This differs from = comparisons:

Consequences:

• Multi-column comparisons are easy with EXCEPT. SELECT Email, Phone, City FROM a EXCEPT SELECT Email, Phone, City FROM b correctly treats two NULL phones as equal. Writing the same with NOT EXISTS needs (a.Phone = b.Phone OR (a.Phone IS NULL AND b.Phone IS NULL)) for every nullable column.
• EXCEPT returns distinct rows only. If you need every matching row (with duplicates) or columns that are not being compared, use NOT EXISTS.

SQL Server 2022 and later also support the predicate IS [NOT] DISTINCT FROM, which compares two values treating NULLs as equal — useful in join and WHERE conditions. The course uses it in Week 3.

Part 4 — Comparing data sets and TRY_CAST

Two-way EXCEPT is a standard data-quality check: if A EXCEPT B and B EXCEPT A both return no rows, the two sets are identical (as sets). This is how you prove that a rewritten query returns exactly the same result as the original.

TRY_CAST(expression AS type) converts like CAST, but returns NULL when conversion fails instead of raising an error:

SELECT  TRY_CAST('62.50' AS decimal(10,2)) AS Good,      -- 62.50
        TRY_CAST('N/A'   AS decimal(10,2)) AS Bad,       -- NULL
        CAST('N/A' AS decimal(10,2))       AS Fails;     -- error 8114: Error converting data type varchar to numeric.

Use it whenever you read text from staging tables or files. TRY_CONVERT is the equivalent of CONVERT.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03.

Validation. Expected: 15, 10, 4, 1.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Staging.CustomerImport)                       AS ImportRows,
        (SELECT COUNT(*) FROM Staging.ProductPriceUpdate)                   AS PriceRows,
        (SELECT COUNT(*) FROM Sales.Customer WHERE Email IS NULL)           AS CustomersWithoutEmail,
        (SELECT COUNT(*) FROM Staging.CustomerImport WHERE Email IS NULL)   AS ImportRowsWithoutEmail;

D. Worked examples

Worked example 1 — UNION, UNION ALL and INTERSECT on cities

SELECT COUNT(*) AS UnionAllRows
FROM  (SELECT City FROM Sales.Customer UNION ALL SELECT City FROM Sales.Store) AS x;

SELECT COUNT(*) AS UnionRows
FROM  (SELECT City FROM Sales.Customer UNION SELECT City FROM Sales.Store) AS x;

SELECT City FROM Sales.Customer
INTERSECT
SELECT City FROM Sales.Store
ORDER BY City;

Expected result: 43 (40 customers + 3 stores), 32 distinct cities, and 3 cities with both customers and a store: Edinburgh, London, Manchester.

Worked example 2 — Products sold in one year but not the other

-- Sold in 2024 but not in 2025
SELECT l.ProductID
FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate <  '2025-01-01'
EXCEPT
SELECT l.ProductID
FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= '2025-01-01';

-- Sold in 2025 but not in 2024
SELECT l.ProductID
FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= '2025-01-01'
EXCEPT
SELECT l.ProductID
FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate <  '2025-01-01';

Expected result: product 30 (the bell tent, discontinued at the end of 2024) and product 28 (the Kids Daypack, launched May 2025). INTERSECT of the same two queries returns 27 products sold in both years. Notice there is no DISTINCT anywhere: the operators removed the hundreds of duplicate product IDs.

Worked example 3 — Customers who use both channels

SELECT CustomerID FROM Sales.SalesOrder WHERE SalesChannel = 'Online'
INTERSECT
SELECT CustomerID FROM Sales.SalesOrder WHERE SalesChannel = 'Store';

Expected result: 33 customers. Swap INTERSECT for EXCEPT in both directions: customer 35 only orders online, customer 32 only in store.

Worked example 4 — NULLs are “equal” to set operators

SELECT TRIM(Email) AS Email FROM Staging.CustomerImport
INTERSECT
SELECT Email FROM Sales.Customer
ORDER BY Email;

Expected result: 4 rows: NULL, then the emails of Alice Morgan, Ben Carter (in whichever case SQL Server returns from the first query) and Mohammed Rahman.

The NULL row appears because both sides contain a NULL email, and set operators treat NULLs as not distinct. Compare with Day 8’s join on c.Email = TRIM(i.Email), which matched only 3 rows. For an email match, the NULL “match” is meaningless, so filter it out: add WHERE Email IS NOT NULL to either query.

Worked example 5 — Precedence

SELECT n FROM (VALUES (1), (2)) AS A(n)
UNION
SELECT n FROM (VALUES (2), (3)) AS B(n)
INTERSECT
SELECT n FROM (VALUES (3), (4)) AS C(n)
ORDER BY n;

(SELECT n FROM (VALUES (1), (2)) AS A(n)
 UNION
 SELECT n FROM (VALUES (2), (3)) AS B(n))
INTERSECT
SELECT n FROM (VALUES (3), (4)) AS C(n)
ORDER BY n;

Expected result: 1, 2, 3 and then 3.

Worked example 6 — A combined activity feed (UNION ALL)

WITH OrderEvents AS
(
    SELECT  'Order' AS EventType, o.OrderDate AS EventDate, o.OrderID,
            ROUND(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 2) + o.ShippingCost AS Amount
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = 1 AND o.OrderDate >= '2025-11-01'
    GROUP BY o.OrderDate, o.OrderID, o.ShippingCost
)
SELECT EventType, EventDate, OrderID, CAST(Amount AS decimal(10,2)) AS Amount
FROM   OrderEvents
UNION ALL
SELECT 'Payment', p.PaymentDate, p.OrderID, p.Amount
FROM   Sales.Payment    AS p
JOIN   Sales.SalesOrder AS o ON o.OrderID = p.OrderID
WHERE  o.CustomerID = 1 AND p.PaymentDate >= '2025-11-01'
ORDER BY EventDate, EventType, OrderID;

Expected result: 8 rows: an Order and a Payment on 24 November (1346, 33.98), 30 November (1349, 328.95) and 3 December (1350, 615.60), then two unpaid orders: 1372 (147.59) and 1373 (683.10).

What to notice: the column names come from the first query; the second query’s 'Payment' literal needs no alias. UNION ALL is correct — an order and its payment with the same amount and date differ only by EventType, but other events could be identical and should all appear. The CAST keeps the two Amount types aligned for readability; SQL Server would otherwise choose the higher-precedence decimal type automatically.

Worked example 7 — TRY_CAST on staging prices

SELECT  RowID,
        ProductCode,
        NewListPriceText,
        TRY_CAST(NewListPriceText AS decimal(10,2)) AS NewListPrice,
        CASE
            WHEN TRY_CAST(NewListPriceText AS decimal(10,2)) IS NULL THEN 'Not a number'
            WHEN TRY_CAST(NewListPriceText AS decimal(10,2)) <= 0    THEN 'Not positive'
            ELSE 'OK'
        END AS PriceCheck
FROM    Staging.ProductPriceUpdate
ORDER BY RowID;

Expected result: 10 rows. Row 5 (N/A) is Not a number; row 8 (-14.99) is Not positive; the rest are OK. Replace TRY_CAST with CAST and the query fails on row 5 — all or nothing.

E. Guided practical lab — Classify the customer import

Data engineering wants the import classified before loading: which emails are new, which belong to existing customers, which existing customers have changed details, and which import rows can’t be matched at all.

Step 1 — New emails (EXCEPT)

1. Do: Find cleaned import emails that don’t exist in Sales.Customer. Exclude NULLs explicitly.
2. Run:

   USE RetailLab;
   GO
   
   SELECT TRIM(Email) AS Email FROM Staging.CustomerImport WHERE Email IS NOT NULL
   EXCEPT
   SELECT Email FROM Sales.Customer
   ORDER BY Email;

3. Observe: 8 distinct emails: blank.name@example.com, daisy.holt@example.com, emma.wilson@example.com, ines.moreau@example.fr, kofi.mensah@example.com, no.name@example.com, sam.jones@ and tariq.aziz@example.
4. Why it matters: Daisy Holt’s three rows (two different spellings) and Kofi Mensah’s two rows collapsed into one email each: EXCEPT de-duplicates, and the collation ignores case. Two of the eight are obviously invalid email addresses — validation is a separate step (Day 13).
5. Confirm: 8 rows.

Step 2 — Existing customers (INTERSECT)

1. Do: Find import emails that already belong to customers.
2. Run:

   SELECT TRIM(Email) AS Email FROM Staging.CustomerImport WHERE Email IS NOT NULL
   INTERSECT
   SELECT Email FROM Sales.Customer
   ORDER BY Email;

3. Observe: 3 rows — Alice Morgan, Ben Carter, Mohammed Rahman.
4. Why it matters: Without WHERE Email IS NOT NULL, a fourth “match” (NULL) would appear, as in worked example 4.
5. Confirm: 3 + 8 = 11 distinct non-NULL import emails.

Step 3 — Which existing customers’ details differ? (multi-column EXCEPT)

1. Do: For matched emails, compare email, phone, city and country between the import and the customer table.

2. Run:

   SELECT TRIM(i.Email) AS Email, i.Phone, i.City, i.Country
   FROM   Staging.CustomerImport AS i
   WHERE  EXISTS (SELECT 1 FROM Sales.Customer AS c WHERE c.Email = TRIM(i.Email))
   EXCEPT
   SELECT Email, Phone, City, Country
   FROM   Sales.Customer
   ORDER BY Email;

3. Observe: 2 rows:

   Email	Phone	City	Country
   alice.morgan@example.com	+44 20 7946 0999	London	United Kingdom
   BEN.CARTER@EXAMPLE.COM	+44 161 496 0102	Manchester	UK

4. Why it matters: Alice has a new phone number; Ben’s country is written UK instead of United Kingdom. Mohammed Rahman’s row is identical and drops out. The email case difference for Ben is ignored (case-insensitive collation) — only the country differs. Set comparison found field-level changes without a single OR … IS NULL test.

5. Confirm: 2 rows. Explain in one sentence why Ben’s row appears.

Step 4 — Rows that cannot be matched by email

1. Do: List import rows with no usable email.
2. Run:

   SELECT ImportRowID, FullName, Email
   FROM   Staging.CustomerImport
   WHERE  Email IS NULL
   ORDER BY ImportRowID;

3. Observe: 1 row — row 12, Lily Chen.
4. Why it matters: Every import row now falls into exactly one bucket. Rows 1, 2 and 11 are existing customers; row 12 needs manual review; the other 11 rows carry the 8 new emails.
5. Confirm: 3 + 1 + 11 = 15 import rows.

Step 5 — One classification result (UNION ALL)

1. Do: Combine the classifications into one labelled list of emails.
2. Run:

   SELECT 'New' AS Classification, Email
   FROM  (SELECT TRIM(Email) AS Email FROM Staging.CustomerImport WHERE Email IS NOT NULL
          EXCEPT
          SELECT Email FROM Sales.Customer) AS n
   UNION ALL
   SELECT 'Existing', Email
   FROM  (SELECT TRIM(Email) AS Email FROM Staging.CustomerImport WHERE Email IS NOT NULL
          INTERSECT
          SELECT Email FROM Sales.Customer) AS e
   ORDER BY Classification, Email;

3. Observe: 11 rows: 3 Existing, 8 New.
4. Why it matters: UNION ALL is right here because the two inputs cannot overlap — an email is either in the customer table or not. Each set operation sits inside a derived table, so the outer UNION ALL combines finished sets.
5. Confirm: 11 rows.

F. Independent practice

Task 1 — Not yet sold in Edinburgh

List active products (no DiscontinuedDate) that have never been sold in the Edinburgh store (StoreID = 3). Return ProductID and ProductCode.

Success criteria: 3 rows — 5 SLP-002, 28 BPK-003, 29 NAV-004.

Task 2 — Countries without a shop

Return the countries where Ridgeway has customers but no store, sorted alphabetically.

Success criteria: 8 countries, from Denmark to United States. The United Kingdom does not appear.

Task 3 — Salespeople who are also managers

Return the EmployeeID of anyone who has served at least one store order and manages at least one employee. Then return the same set with EXISTS, and compare.

Success criteria: 1 employee — 6 (Liam O’Connor). Both techniques agree.

Task 4 — Customer 1’s changing basket (challenging)

For customer 1 (non-cancelled orders), classify every product they bought in 2024 or 2025 as Repeat (both years), Dropped (2024 only) or New (2025 only). Return Classification and ProductID in one result, sorted by classification and product.

Success criteria: 27 rows — 10 Repeat, 9 Dropped (including product 30, the discontinued bell tent) and 8 New.

Stretch challenge — Which prices would change?

Build the proposed price list from Staging.ProductPriceUpdate: clean the code (UPPER(TRIM(…))), keep the latest row per code (Day 5), keep only prices that convert to a positive number, and only codes that exist in Inventory.Product. Then use set operators to show the before/after pairs for products whose price would actually change: ProductCode, ListPrice as Before rows and as After rows.

Success criteria: 10 rows (5 Before, 5 After) for COK-003, JKT-005, LGT-001, SLP-003 and TNT-001. BPK-001 (N/A), NAV-003 (negative) and TNT-099 (unknown code) are excluded.

G. Solutions and explanations

SELECT ProductID, ProductCode
FROM   Inventory.Product
WHERE  DiscontinuedDate IS NULL
EXCEPT
SELECT p.ProductID, p.ProductCode
FROM   Sales.SalesOrder     AS o
JOIN   Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
WHERE  o.StoreID = 3
ORDER BY ProductID;

SELECT Country FROM Sales.Customer
EXCEPT
SELECT Country FROM Sales.Store
ORDER BY Country;

SELECT EmployeeID FROM Sales.SalesOrder WHERE EmployeeID IS NOT NULL
INTERSECT
SELECT ManagerID FROM HR.Employee;

SELECT e.EmployeeID
FROM   HR.Employee AS e
WHERE  EXISTS (SELECT 1 FROM Sales.SalesOrder AS o WHERE o.EmployeeID = e.EmployeeID)
  AND  EXISTS (SELECT 1 FROM HR.Employee     AS r WHERE r.ManagerID  = e.EmployeeID)
ORDER BY e.EmployeeID;

WITH P2024 AS
(
    SELECT l.ProductID
    FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE  o.CustomerID = 1 AND o.OrderStatus <> 'Cancelled'
      AND  o.OrderDate >= '2024-01-01' AND o.OrderDate < '2025-01-01'
),
P2025 AS
(
    SELECT l.ProductID
    FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE  o.CustomerID = 1 AND o.OrderStatus <> 'Cancelled'
      AND  o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
)
SELECT 'Repeat'  AS Classification, ProductID FROM (SELECT ProductID FROM P2024 INTERSECT SELECT ProductID FROM P2025) AS r
UNION ALL
SELECT 'Dropped', ProductID FROM (SELECT ProductID FROM P2024 EXCEPT SELECT ProductID FROM P2025) AS d
UNION ALL
SELECT 'New',     ProductID FROM (SELECT ProductID FROM P2025 EXCEPT SELECT ProductID FROM P2024) AS n
ORDER BY Classification, ProductID;

WITH Latest AS
(
    SELECT  UPPER(TRIM(ProductCode))                    AS ProductCode,
            TRY_CAST(NewListPriceText AS decimal(10,2)) AS NewListPrice,
            ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(ProductCode)) ORDER BY RowID DESC) AS rn
    FROM    Staging.ProductPriceUpdate
    WHERE   ProductCode IS NOT NULL
),
Proposed AS
(
    SELECT  l.ProductCode, l.NewListPrice AS ListPrice
    FROM    Latest AS l
    WHERE   l.rn = 1
      AND   l.NewListPrice > 0                                   -- NULL (not a number) fails this too
      AND   EXISTS (SELECT 1 FROM Inventory.Product AS p WHERE p.ProductCode = l.ProductCode)
),
CurrentPrices AS
(
    SELECT  p.ProductCode, p.ListPrice
    FROM    Inventory.Product AS p
    WHERE   p.ProductCode IN (SELECT ProductCode FROM Proposed)
)
SELECT 'Before' AS Version, ProductCode, ListPrice
FROM  (SELECT ProductCode, ListPrice FROM CurrentPrices
       EXCEPT
       SELECT ProductCode, ListPrice FROM Proposed) AS b
UNION ALL
SELECT 'After', ProductCode, ListPrice
FROM  (SELECT ProductCode, ListPrice FROM Proposed
       EXCEPT
       SELECT ProductCode, ListPrice FROM CurrentPrices) AS a
ORDER BY ProductCode, Version DESC;          -- Before, then After

H. Knowledge check

1. Understanding. A has 10 rows (2 duplicates), B has 5 rows, and 3 distinct rows appear in both. How many rows do A UNION ALL B and A INTERSECT B return?

2. Query interpretation. What does this return?

   SELECT CAST(NULL AS int) AS x
   EXCEPT
   SELECT CAST(NULL AS int);

3. Debugging. This fails. Why?

   SELECT CustomerID, City FROM Sales.Customer
   UNION
   SELECT StoreID FROM Sales.Store;

4. Debugging. A developer writes ORDER BY StoreName at the end of SELECT City AS Place FROM Sales.Customer UNION SELECT StoreName FROM Sales.Store. It fails with Invalid column name. Why, and what should they write?

5. Understanding. When would you choose NOT EXISTS instead of EXCEPT to find rows in A that are not in B?

I. Troubleshooting and cleanup

Cleanup. Day 9 is read-only; nothing to clean up. Keep RetailLab unchanged for Day 10.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 returned 43, 32 and 3 rows.
• Worked example 4 showed a NULL “match” and you know how to remove it.
• The lab classified all 15 import rows (3 existing, 1 without email, 11 rows carrying 8 new emails) and found 2 changed customers.
• Tasks 1–4 match the success criteria (3, 8, 1 and 27 rows).
• You can explain when UNION ALL is the correct choice.

Recap

• UNION (distinct), UNION ALL (everything), INTERSECT (in both, distinct), EXCEPT (in first not second, distinct).
• Same column count; compatible types by position; names from the first query; one ORDER BY at the end.
• INTERSECT binds tighter than UNION/EXCEPT; parenthesise mixed operators.
• Set operators treat NULLs as equal and remove duplicates — unlike =, IN and EXISTS.
• Two-way EXCEPT proves two results identical; multi-column EXCEPT detects changed rows.
• TRY_CAST returns NULL instead of failing on bad text.

Next: Day 10 — The APPLY operator: CROSS APPLY, OUTER APPLY and table-valued functions.

References (Microsoft Learn)

• Set operators – UNION (Transact-SQL)
• Set operators – EXCEPT and INTERSECT (Transact-SQL)
• TRY_CAST (Transact-SQL)
• IS [NOT] DISTINCT FROM (Transact-SQL)
• Data type precedence (Transact-SQL)
• Collation and Unicode support