Ask the Expert

Mixing ORs with NOTs

I want to return only KTAN8 that do not have DEL, DELA, MKT. I keep getting all KTAN8 with DEL, DELA, or MKT along with other modules that the KTAN8 has.

       WHERE KT$PMD <> 'DEL' 
          or KT$PMD <> 'DELA' 
          or KT$PMD <> 'MKT' ) 

    Requires Free Membership to View

This is a nice example of the difficulties that can arise when mixing ORs with NOTs.

Suppose KT$PMD is equal to 'DEL'. Therefore it will fail the first test, KT$PMD<>'DEL'. Since 'DEL' is equal to 'DEL', therefore 'DEL'<>'DEL' is false.

However, it will pass the second test, KT$PMD<>'DELA'. Since 'DEL' is not equal to 'DELA', therefore 'DEL'<>'DELA' is true. And because you have combined the conditions with ORs, it turns out that the WHERE clause must evaluate to true for every single row, no matter what value KT$PMD has. If it's equal to one of those values, it's automatically not equal to the other two, thus the WHERE clause is true.

You don't really need a subquery for this. All you need to do is make sure that the value isn't equal to any of the given values.

There are several ways to do it:

select KTAN8
     , KT$MPC
     , KT$PMD
  from IRIS01.FTDR2PDTA.F55431                 
 where KT$PMD <> 'DEL' 
   and KT$PMD <> 'DELA' 
   and KT$PMD <> 'MKT' 
select KTAN8
     , KT$MPC
     , KT$PMD
  from IRIS01.FTDR2PDTA.F55431                 
 where not
     ( KT$PMD = 'DEL' 
    or KT$PMD = 'DELA' 
    or KT$PMD = 'MKT' )
select KTAN8
     , KT$MPC
     , KT$PMD
  from IRIS01.FTDR2PDTA.F55431                 
 where not KT$PMD 
    in ( 'DEL','DELA','MKT' )

My preference is for the last one, because it's easier to understand. Any time you can simplify a complex expression which contains NOTs, ANDs, and ORs, it will be easier to understand and therefore easier to maintain if when changes are required.

This was first published in November 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: