changing the order of OR clauses changes results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

changing the order of OR clauses changes results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-09 : 00:59:18

Kevin writes "Hi,

I have a simple enough query which gives different results depending on the order in which 4 different "OR" statements are put into a WHERE clause. Why is this so?

Heres the data :
Table1 and Table2 each have 2 columns : clientID and DED. Both are nullable.

My sample data is :

Table 1 : 
clientid, DED
--------  ---
1         aaa
2         bbb
3         ccc

Table 2 : 
clientid, DED
--------  ---
1         aaa
2         bbb
3         (null)

This query attempts to produce all rows where there is a difference in the DED column.:

select 
TABLE2.clientid , 
TABLE2.ded as table2DED, 
table1.ded as table1DED
FROM TABLE2 Join table1 ON
  table1.CLIENTID = TABLE2.clientid
WHERE (
( /* condition 3 */TABLE2.ded <> table1.ded)  OR   
  ( /* Condition 1 */TABLE2.ded is not null AND table1.ded is null )   OR
  ( /* Condition 2 */TABLE2.ded is null AND table1.ded is not null )    OR
 (/*Condition 4*/ table1.ClientID Is Null) 
)

clientid    table2DED  table1DED  
----------- ---------- ---------- 
3           NULL       ccc     

(1 row(s) affected)

This query should return 1 row (relating to clientid 3), and indeed it does. However, if the order of the WHERE clause is changed such that /* Condition 1 */ comes first, no rows are returned. :

select 
TABLE2.clientid , 
TABLE2.ded as table2DED, 
table1.ded as table1DED
FROM TABLE2 Join table1 ON
  table1.CLIENTID = TABLE2.clientid
WHERE (
  ( /* Condition 1 */TABLE2.ded is not null AND table1.ded is null )   OR
  ( /* Condition 2 */TABLE2.ded is null AND table1.ded is not null )    OR
( /* condition 3 */TABLE2.ded <> table1.ded)  OR   
 (table1.ClientID Is Null) 
)

clientid    table2DED  table1DED  
----------- ---------- ---------- 

(0 row(s) affected)

I have noticed that condition 4 is nonsensical, and if I take it out, everything works OK. However I dont think that including condition 4 should cause a different result. So purely as an academic exercise, can anyone explain why this is so?

The system is SQL Server 7.00.623, NT4 SP6"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources