Q

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.

SELECT
    KTAN8,
    KT$MPC,
    KT$PMD
FROM
    IRIS01.FTDR2PDTA.F55431                 
WHERE
    EXISTS 
     ( SELECT KTAN8 FROM IRIS01.FTDR2PDTA.F55431 
       WHERE KT$PMD <> 'DEL' 
          or KT$PMD <> 'DELA' 
          or KT$PMD <> 'MKT' ) 

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

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close