Avoiding null comparisons in where clause

It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given.

If that statement is true, what is a better way to write the following?

SELECT * FROM dbo.DpdRoute 
    WHERE DestinationCountry =@COUNTRY 
    AND (  (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL) 
        OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
        OR (BeginPostCode = @POSTCODE))

I'm looking for a list of routes, for a given country where a postcode is either within a range of postcodes, equal to the min or max post code, or the min or max post codes are both null.

How does one "not compare to null" when the comparison is specifically about "nulls"?

If you have an index on DestinationCountry,BeginPostCode,ENDPOSTCODE then your original query seeks into the DestinationCountry part but no further.

You may find better performance rewriting as an UNION ALL if you have that index.

The below can seek into non overlapping parts of this index.

• An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) = (@COUNTRY,NULL, NULL)

• An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) = (@COUNTRY,NULL, @POSTCODE)

• An equality seek on (DestinationCountry) = (@COUNTRY) and then a range seek on the secondary key `BeginPostCode

__

WITH Country
     AS (SELECT *
         FROM   dbo.DpdRoute
         WHERE  DestinationCountry = @COUNTRY) 
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE IS NULL
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE = @POSTCODE
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode = @POSTCODE
              OR ( BeginPostCode = @POSTCODE
                   AND ENDPOSTCODE IS NULL ) );

StackExchange Database Administrators Q#157844, answer score: 4