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 AND ENDPOSTCODE >= @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 <= @POSTCODE with a residual predicate on all rows that match.

__

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
       AND ( ENDPOSTCODE >= @POSTCODE
              OR ( BeginPostCode = @POSTCODE
                   AND ENDPOSTCODE IS NULL ) ); 

(    (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL) 
  OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
  OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
  OR (BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE) 
)

That might be the same as

    isnull(BeginPostCode, @POSTCODE) <= @POSTCODE 
and isnull(ENDPOSTCODE,   @POSTCODE) >= @POSTCODE 

I could be wrong. I think every condition that need to pass does pass.
Not sure if something that should fail will get through.

It fails on BeginPostCode IS NULL and ENDPOSTCODE >= @POSTCODE

This might work

    (     isnull(BeginPostCode, @POSTCODE) = @POSTCODE 
      and isnull(ENDPOSTCODE,   @POSTCODE) = @POSTCODE ) 
 OR ( BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE )