SQL Server JOIN with optional NULL values
- by Paul McLoughlin
Imagine that we have two tables as follows:
Trades
(
TradeRef INT NOT NULL,
TradeStatus INT NOT NULL,
Broker INT NOT NULL,
Country VARCHAR(3) NOT NULL
)
CTMBroker
(
Broker INT NOT NULL,
Country VARCHAR(3) NULL
)
(These have been simplified for the purpose of this example). Now, if we wish to join these two tables on the Broker column, and if a country exists in the CTMBroker table on the Country, we have the following two choices:
SELECT T.TradeRef,T.TradeStatus
FROM Trades AS T
JOIN CTMBroker AS B ON B.Broker=T.Broker AND ISNULL(B.Country, T.Country) = T.Country
or
SELECT T.TradeRef,T.TradeStatus
FROM Trades AS T
JOIN CTMBroker AS B ON B.Broker=T.Broker AND (B.COUNTRY=T.Country OR B.Country IS NULL)
These are both logically equivalent, however in this specific circumstance for our database (SQL Server 2008, SP1) two different execution plans are produced for these two queries with the second version significantly outperforming the first version in terms of both time and logical reads.
My question really is as follows: as a general rule would (2) be preferred to (1), or does this just happen to be exploiting some particular idiosyncracy of the optimiser in 2008 SP1 (that could therefore change with future versions of SQL Server).
