One NULL in a Unique Key, and None Across a Join
You put a UNIQUE constraint on a column to stop duplicates, insert two rows that both leave that column NULL, and the second insert fails with a unique key violation. For uniqueness, SQL Server rejects the second NULL as a duplicate. Later you join two tables on a nullable key, and the rows where the key is NULL on both sides do not match. A unique constraint rejects a second NULL, while an equality join does not match two NULLs, so NULL is handled by two different rules in two places you rely on to be strict. Both are places NULL behaves in a way people do not expect, which is what this series is about.
This is Part 8 of The UNKNOWN Problem, a series on NULL traps in T-SQL. This post compares how a unique constraint and a join each handle NULL, because they do not handle it the same way.
Start with the unique key. A table with a nullable code column and a UNIQUE constraint on it.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE dbo.supplier ( supplier_id int NOT NULL CONSTRAINT pk_supplier PRIMARY KEY , tax_code varchar(20) NULL CONSTRAINT uq_supplier_tax UNIQUE ); INSERT INTO dbo.supplier (supplier_id, tax_code) VALUES (1, NULL); /* ok */ INSERT INTO dbo.supplier (supplier_id, tax_code) VALUES (2, NULL); /* fails */ |
The second insert fails with a unique key violation. A SQL Server UNIQUE constraint allows one NULL in the column and rejects a second[1], because for uniqueness it treats NULL as a key value and a second single-column NULL key is a duplicate. This surprises people who expect either no NULLs or any number of NULLs. A nullable column with a single-column UNIQUE constraint can hold one NULL. (A composite unique key follows the same idea per combination: (1, NULL) conflicts with another (1, NULL), but not with (2, NULL).)
That is a problem when NULL means “not assigned yet” and more than one row can legitimately be unassigned. If several suppliers have no tax code on file, the first NULL is stored and every later row with a NULL tax_code is rejected. The fix is a filtered unique index that enforces uniqueness only on the rows that have a value.[2]
|
1 2 3 4 5 |
ALTER TABLE dbo.supplier DROP CONSTRAINT uq_supplier_tax; CREATE UNIQUE INDEX uq_supplier_tax ON dbo.supplier (tax_code) WHERE tax_code IS NOT NULL; |
Now any number of rows can have a NULL tax_code, and the ones that do have a code are still unique. The WHERE tax_code IS NOT NULL predicate excludes the NULL rows from the index, so the uniqueness check does not apply to them. This is the usual way to express “unique when present” in SQL Server, and the plain UNIQUE constraint does not mean that. One operational note: a filtered index needs the ANSI SET options set correctly when you create it and when you modify the indexed rows (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER ON, NUMERIC_ROUNDABORT OFF), which the common client drivers already do, and dropping the original constraint first also means no foreign key can depend on it.
Now the join, where NULL is handled differently. Two rows with a NULL key do not match each other in an equi-join, because the join predicate is an equality comparison and NULL = NULL is UNKNOWN, not TRUE.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE dbo.supplier_import ( import_id int NOT NULL CONSTRAINT pk_supplier_import PRIMARY KEY , tax_code varchar(20) NULL ); SELECT s.supplier_id, x.import_id FROM dbo.supplier AS s INNER JOIN dbo.supplier_import AS x ON x.tax_code = s.tax_code; /* a NULL tax_code on either side does not match */ |
Any supplier whose tax_code is NULL, and any import row whose tax_code is NULL, drops out of the inner join, because the equality predicate is not TRUE when either side is NULL.[3] This is the same three-valued logic a WHERE filter uses: a row is kept only when the predicate is TRUE. So a unique constraint counts a second NULL as a duplicate, while an equality join does not treat two NULLs as a match. UNIQUE, GROUP BY, and DISTINCT group NULLs together as if they were one key value, while = in a join compares them under three-valued logic and gets UNKNOWN. It is worth keeping straight which of the two behaviors you are relying on.
To match rows when both keys are NULL, use a NULL-safe comparison. On SQL Server 2022 and later that is IS NOT DISTINCT FROM.
|
1 2 |
-- SQL Server 2022+ ... ON x.tax_code IS NOT DISTINCT FROM s.tax_code; |
Before 2022 you write the NULL case out, ON x.tax_code = s.tax_code OR (x.tax_code IS NULL AND s.tax_code IS NULL). That OR form can change the join strategies and cardinality estimates the optimizer has to work with. It may still use an index, so check the actual plan for your data and version, and IS NOT DISTINCT FROM reads more clearly on 2022+ without being a guarantee of a seek either. Most of the time, though, matching NULL to NULL in a join is not what you want. NULL means unknown, and two unknown keys are not evidence that they are the same supplier. The default of not matching them is usually correct, so the goal is to know it is happening rather than be surprised by the missing rows.
The reason these two catch people is that a unique key and a join both read as strict rules, and they behave predictably on rows that have values. A unique constraint accepts the first NULL without complaint, and an inner join leaves out the rows with NULL keys without an error. The unique behavior shows up when a second unassigned row is inserted and gets rejected, and the join behavior shows up when the result has fewer rows than you expected.
Next up is Part 9, the last one, INTERSECT and EXCEPT, where NULL is treated as equal to NULL, the one comparison in this series that lines up with what people expect, and how to use it on purpose. It also links back to the whole series as a recap.
If a lone-NULL unique key or a join that dropped your NULL-keyed rows has cost you time, I would like to hear about it. You can find me on Bluesky and LinkedIn.
References
- Unique Constraints and Check Constraints - Microsoft Learn. Documents that a UNIQUE constraint treats NULLs as equal and allows only one. ↩
- Create Filtered Indexes - Microsoft Learn. Covers filtered indexes, including the unique-when-present pattern with WHERE col IS NOT NULL. ↩
- NULL Complexities, Part 3 - SQLPerformance, Itzik Ben-Gan. Shows equi-joins dropping NULL keys and the EXISTS with INTERSECT workaround for NULL-safe joins. ↩