Skip to content
Advertisement

Problem with field not equal to null in case statement

So I have EXISTS in huge query which looks like this:

EXISTS(
   SELECT 
      * 
   FROM 
      ExistTable
   WHERE
      ExTableFieldA = @SomeGuid AND
      ExTableFieldB = MainTableFieldB AND
      ExTableFieldA <> (
                         CASE
                           WHEN MainTableFieldZ = 10 THEN MainTableFieldYYY
                           ELSE NULL
                         END
                       )
)

The problem comes from ELSE part of CASE statement, this ExTableFieldA <> NULL will be always false. I could easily write another parameter @EmptyGuid and make it equal to '00000000-0000-0000-0000-000000000000' and everything will work but is this the best approach ?

Pretty much I want to execute another check into the exist for the small size of the records which return the “main” query.

Advertisement

Answer

How about removing the case and just using boolean logic?

WHERE ExTableFieldA = @SomeGuid AND
      ExTableFieldB = MainTableFieldB AND
      (MainTableFieldZ <> 10 OR ExTableFieldA <> MainTableFieldYYY)

I would also recommend that you qualify the column names by including the table alias.

Note: This does assume that MainTableFieldZ is not NULL. If that is a possibility than that logic can easily be incorporated.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement