Logic question in a WHERE clause using IIF?

Question

Logic question in a WHERE clause using IIF?

DaveBriCam

SSCarpal Tunnel

Points: 4316
More actions
August 21, 2026 at 8:10 pm
Go to Answer
#4945168

Thanks in advance,
I'm trying to avoid using IIF for performance reasons, but the code has me stumped.
I'm trying to add a filter in my WHERE clause that basically says "omit records if [BillingReason] in (1, 2, 3) AND ([NewInsID] IS NOT NULL AND [OldInsID] IS NOT NULL)". For example:
[BillingReason] = 1, [NewInsID] = Null, [OldInsID] =Null -- Omit this record
[BillingReason] = 4, [NewInsID] = Null, [OldInsID] =Null -- Include this record
[BillingReason] = 1, [NewInsID] = 123456, [OldInsID] = 456789 -- Include this record
[BillingReason] = 2, [NewInsID] = 123456, [OldInsID] = 456789 -- Include this record
[BillingReason] = 3, [NewInsID] = Null, [OldInsID] = Null -- Omit this record
Jonathan AC Roberts

SSCoach

Points: 18410
More actions
August 22, 2026 at 12:55 am
Answer
#4945462
Just wrap the whole thing in a NOT:
```
WHERE NOT (
    [BillingReason] IN (1, 2, 3)
    AND [NewInsID] IS NOT NULL
    AND [OldInsID] IS NOT NULL
)
```
Or use De Morgan's law
```
WHERE [BillingReason] NOT IN (1, 2, 3)
   OR [NewInsID] IS NULL
   OR [OldInsID] IS NULL
```
If BillingReason can itself be NULL the two forms can behave differently because of SQL's three-valued logic. The NOT (...) version is also subject to UNKNOWN in that case, so if BillingReason IS NULL should definitely be retained
```
WHERE [BillingReason] IS NULL
   OR NOT (
       [BillingReason] IN (1, 2, 3)
       AND [NewInsID] IS NOT NULL
       AND [OldInsID] IS NOT NULL
   )
```

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2278 More actions · Answer 1

DaveBriCam wrote:

I'm trying to add a filter in my WHERE clause that basically says "omit records if [BillingReason] in (1, 2, 3) AND ([NewInsID] IS NOT NULL AND [OldInsID] IS NOT NULL)".

The topic says "omit records if..." but the t-sql WHERE clause, in effect, says "keep rows if..." (all predicate conditions are TRUE). The examples show you want to omit the two different conditions when: 1) BillingReason IN (1,2,3), and 2) both ID's are NULL. To implement that logic in the WHERE clause (where all conditions must evaluate to TRUE) you could apply De Morgan's Law and invert the expression

WHERE BillingReason NOT IN (1,2,3)
      OR NOT (NewInsID IS NULL AND OldInsID IS NULL)

If BillingReason is a NULL'able column then any NULL values would be omitted. If you want NULL BillingReason rows included add an explicit "OR BillingReason IS NULL" (rather than wrapping the column in ISNULL()/COALESCE(), which can prevent sargability). Maybe like this

WHERE (BillingReason NOT IN (1,2,3) OR BillingReason IS NULL)
      OR NOT (NewInsID IS NULL AND OldInsID IS NULL)

This reply was modified 3 weeks, 1 days ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

naumon765 SSC Journeyman Points: 84 More actions · Answer 2

naumon765

SSC Journeyman

Points: 84

August 22, 2026 at 12:27 pm

#4946519

Screenshot 2026-08-22 175659

frederico_fonseca SSCoach Points: 16347 More actions · Answer 3

another worthless image that can't be fully read. put plain text next time please.

naumon765 SSC Journeyman Points: 84 More actions · Answer 4

WHERE 

          BillingReason NOT IN (1,2,3)
          OR 

        COALESCE(NewInsID, OldInsID) IS NOT NULL

This reply was modified 3 weeks ago by naumon765.
This reply was modified 3 weeks ago by naumon765.

naumon765 SSC Journeyman Points: 84 More actions · Answer 5

WHERE 

            BillingReason NOT IN (1,2,3)
              OR 

          EXISTS (SELECT 1 FROM Insurance i WHERE i.ID IN (NewInsID, OldInsID))

naumon765 SSC Journeyman Points: 84 More actions · Answer 6

-- Filtered Index

CREATE NONCLUSTERED INDEX IX_BillingReason_Filtered
ON dbo.YourTable (BillingReason)
WHERE BillingReason NOT IN (1,2,3);

Steve Collins SSCrazy Points: 2278 More actions · Answer 7

frederico_fonseca wrote:

another worthless image that can't be fully read. put plain text next time please.

Yeah 4 worthless answers in one topic. 3 of them are basically unrelated to the question being asked. The COALESCE version is dangerous tho. It happens to return correct results for these examples but it encodes a different rule — 'keep if either ID has a value' vs 'keep if both IDs have values.' When one ID is NULL and the other isn't it will return incorrect results

Jonathan and me have slightly different answers but both follow the same reasoning. The topic itself is inconsistent between the description and the examples. The description says to omit when both ID's are not null. The examples do the opposite. Jonathan correctly coded the t-sql based on the description. My answer coded based on the examples. The other difference is Jonathan applies De Morgan's Law twice whereby the 2 parentheses were removed from the compound condition (on ID's)

-- the original conditions (from the description) are to omit if these 2 conditions are true
 [BillingReason] in (1, 2, 3) 
AND 
 ([NewInsID] IS NOT NULL AND [OldInsID] IS NOT NULL)

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

naumon765 SSC Journeyman Points: 84 More actions · Answer 8

another thing is #the rowcount of each filter # design depends on that # 10 or 100,000

DaveBriCam SSCarpal Tunnel Points: 4316 More actions · Answer 9

Thanks!... this worked perfectly after a slight modification - changing the IS NOT NULL to IS NULL:

WHERE NOT (

[BillingReason] IN (1, 2, 3)

AND [NewInsID] IS NULL

AND [OldInsID] IS NULL

)