Today I learned you can't join tables on NULLs because SQL Server deems a NULL to be an ambiguous quantity so two NULLs are not the same NULL. You can force SQL Server to do the join by doing something like this
... colors c JOIN points p ON (c.color = p.color OR (c.color IS NULL AND p.color IS NULL))
This example assumes only one team will have a NULL color.
CREATE TABLE colors (
team varchar(16) NOT NULL,
color varchar(32) NULL,
)
CREATE TABLE points (
color varchar(32) NULL,
points int
)
INSERT INTO colors VALUES ('lobsters','red')
INSERT INTO colors VALUES ('swans','white')
INSERT INTO colors VALUES ('jellyfish',NULL)
INSERT INTO points VALUES ('red',100)
INSERT INTO points VALUES ('white',90)
INSERT INTO points VALUES (NULL,80)
SELECT * FROM colors c JOIN points p ON c.color = p.color
-- returns:
--lobsters red red 100
--swans white white 90
SELECT * FROM colors c JOIN points p ON (c.color = p.color OR (c.color IS NULL AND p.color IS NULL))
-- returns:
--lobsters red red 100
--swans white white 90
--jellyfish NULL NULL 80
