EXCEPT Operator in SQL Server: Distinct Rows and Differences

The EXCEPT operator returns the rows of one query that are missing from another query, with duplicates removed. That last part explains an old trick: EXCEPT against an empty result returns a distinct list.

What EXCEPT Does

Think of two baskets of fruit. EXCEPT hands you what is in the first basket and not in the second. If the first basket holds three identical apples, you get one apple back. Both queries need the same number of columns, with compatible data types. The result takes its column names from the first query.

The demo is a tea shop. One table holds last week’s orders and another holds this week’s. Maya and Priya appear twice in last week’s table, which is the duplicate data EXCEPT will clean up. Sam’s rows have no item, so Item is NULL there. The script creates a database named ExceptSetDemo for this post only.

IF DB_ID(N'ExceptSetDemo') IS NULL CREATE DATABASE ExceptSetDemo;
GO
USE ExceptSetDemo;
GO
DROP TABLE IF EXISTS dbo.OrdersLastWeek, dbo.OrdersThisWeek;
CREATE TABLE dbo.OrdersLastWeek (Customer nvarchar(30) NOT NULL, Item nvarchar(30) NULL);
CREATE TABLE dbo.OrdersThisWeek (Customer nvarchar(30) NOT NULL, Item nvarchar(30) NULL);
INSERT INTO dbo.OrdersLastWeek (Customer, Item)
VALUES (N'Maya', N'Green tea'), (N'Maya', N'Green tea'), (N'Leo', N'Chai'),
       (N'Priya', N'Mint tea'), (N'Priya', N'Mint tea'), (N'Sam', NULL), (N'Sam', NULL);
INSERT INTO dbo.OrdersThisWeek (Customer, Item)
VALUES (N'Maya', N'Green tea'), (N'Leo', N'Ginger tea'), (N'Sam', NULL);

Rows in One Query but Not the Other

Which orders from last week didn’t come back this week? Put last week’s query first and this week’s query after the keyword.

SELECT Customer, Item FROM dbo.OrdersLastWeek
EXCEPT
SELECT Customer, Item FROM dbo.OrdersThisWeek;

Two rows come back. Leo’s chai wasn’t ordered again, and neither was Priya’s mint tea. Priya appears once, although last week’s table holds two rows for Priya. EXCEPT removes duplicates from its result, so it behaves like DISTINCT on the left side. The order of the two queries matters. Swap them and you ask the opposite question.

SELECT Customer, Item FROM dbo.OrdersThisWeek
EXCEPT
SELECT Customer, Item FROM dbo.OrdersLastWeek;

This time one row comes back: Leo tried something new. To see every difference in one list, join both directions with UNION ALL. Each EXCEPT sits in parentheses.

(SELECT Customer, Item FROM dbo.OrdersLastWeek EXCEPT SELECT Customer, Item FROM dbo.OrdersThisWeek)
UNION ALL
(SELECT Customer, Item FROM dbo.OrdersThisWeek EXCEPT SELECT Customer, Item FROM dbo.OrdersLastWeek);

The related operator INTERSECT returns the rows that appear in both queries. Here it returns Maya with Green tea and Sam with a NULL item. T-SQL has no EXCEPT ALL, so a duplicate can’t survive in an EXCEPT result. EXCEPT and INTERSECT arrived in SQL Server 2005.

The EXCEPT Operator as a Distinct Trick

If EXCEPT removes duplicates, then EXCEPT against an empty result gives a distinct list. The condition WHERE 1 = 0 makes the second query empty. The three statements below ask for the same thing in three ways. Each one is its own batch, so SQL Server keeps a separate plan for each.

SELECT Customer, Item FROM dbo.OrdersLastWeek
EXCEPT
SELECT Customer, Item FROM dbo.OrdersLastWeek WHERE 1 = 0;
GO
SELECT DISTINCT Customer, Item FROM dbo.OrdersLastWeek;
GO
SELECT Customer, Item FROM dbo.OrdersLastWeek GROUP BY Customer, Item;

All three return these four rows. Do they run the same way? The query below reads the plan hash of each statement from the plan cache. Equal hashes mean equal plans.

SELECT CASE WHEN st.text LIKE N'%1 = 0%' THEN N'EXCEPT with 1 = 0'
            WHEN st.text LIKE N'%SELECT DISTINCT%' THEN N'DISTINCT' ELSE N'GROUP BY' END AS WrittenWith,
       qs.query_plan_hash AS PlanHash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%dbo.OrdersLastWeek%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
  AND (st.text LIKE N'%1 = 0%' OR st.text LIKE N'%SELECT DISTINCT%' OR st.text LIKE N'%GROUP BY Customer, Item%')
ORDER BY WrittenWith;

The hashes match, so SQL Server built the same plan shape for all three. Your hash value will differ, but the three rows will still match each other. EXCEPT with an empty second query has no performance advantage. It also hides the intent, which makes it a poor choice for plain deduplication. UNION against an empty set also returns these four rows, because UNION removes duplicates too. Its plan hash differs from the DISTINCT one on the test server, so it isn’t the same plan shape.

How NULLs Behave

EXCEPT treats two NULLs as equal. That is why Sam, whose item is NULL in both weeks, vanished from the first result. Other ways to ask the same question don’t agree. The next query uses NOT EXISTS with an equals sign on each column.

SELECT Customer, Item FROM dbo.OrdersLastWeek AS a
WHERE NOT EXISTS (SELECT 1 FROM dbo.OrdersThisWeek AS b WHERE b.Customer = a.Customer AND b.Item = a.Item);

Two things changed. Sam’s rows came back, because NULL equals NULL is not true. Priya’s duplicate also came back, because NOT EXISTS doesn’t remove duplicates. NOT IN is worse. When the subquery returns a NULL, the whole test becomes unknown, and no row passes.

SELECT Customer, Item FROM dbo.OrdersLastWeek
WHERE Item NOT IN (SELECT Item FROM dbo.OrdersThisWeek);

This query returns zero rows, even though Leo’s chai is plainly missing from this week. This is one reason I reach for EXCEPT when I compare two sets of rows.

Column Rules and Sorting

Both queries need the same number of columns. If they differ, SQL Server stops with Msg 205. ORDER BY belongs at the end and applies to the whole result, using the first query’s column names.

SELECT Customer, Item FROM dbo.OrdersLastWeek EXCEPT SELECT Customer FROM dbo.OrdersThisWeek;

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

You could argue that the EXCEPT operator is a clumsy way to get distinct rows, and you’d be right. For plain deduplication, DISTINCT is clearer, and the plan is the same. EXCEPT earns its place when the second query holds real rows, such as the rows a load should have copied. Then the rows left over are the problem rows.

What to Remember

The EXCEPT operator answers one question: which rows are in the first result and not in the second. It removes duplicates, treats NULLs as equal and needs matching columns. For a full comparison, run it in both directions.

Use DISTINCT when you only want distinct rows. When you’re done with the demo, run the cleanup script to drop the database.

USE master;
GO
IF DB_ID(N'ExceptSetDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ExceptSetDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ExceptSetDemo;
END;

EXCEPT is not a shortcut for DISTINCT, it is a question about what went missing.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.