views:

499

answers:

3

Are the two statements below equivalent?

SELECT [...] FROM [...] WHERE some_col in (1,2,3,4,5) AND some_other_expr

and

SELECT [...] FROM [...] WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr

Is there some sort of truth table I could use to verify this? Thanks.

+5  A: 

And has precedence over Or, so, even if A <=> A1 Or A2

 Where A And B

is not the same as

 Where A1 Or A2 And B,

because that would be Executed as

 Where A1 or (A2 And B)

and what you want, to make them the same, is

 Where (A1 Or A2) And B
Charles Bretana
Ouch... thank you... glad I asked this question :)Here's some further reference that says exactly the same thing: http://www.praetoriate.com/t_garmany_easysql_sql_logical_operators.htmTime to use some parentheses.
nc
A: 

They are equivalent. You didn't specify a database type so here is an example in SQL Server 2005.

with temp as
(
 select 'a' as type, 1 as some_col
 union
 select 'b' as type, 2 as some_col
 union
 select 'c' as type, 3 as some_col
 union
 select 'd' as type, 4 as some_col
 union
 select 'e' as type, 5 as some_col
 union
 select 'f' as type, 6 as some_col

)

select * from temp where some_col in (1,2) or some_col in (3)
select * from temp where some_col in (1,2,3)
thomas
see the marked answer - this is incorrect
nc
This works ony if you don't have an AND like the question
gbn
+1  A: 

I'll add 2 points:

  • "IN" is effectively serial ORs with parentheses around them
  • AND has precedence over OR in every language I know

So, the 2 expressions are simply not equal.

WHERE some_col in (1,2,3,4,5) AND some_other_expr
--to the optimiser is this
WHERE
     (
     some_col = 1 OR
     some_col = 2 OR 
     some_col = 3 OR 
     some_col = 4 OR 
     some_col = 5
     )
     AND
     some_other_expr

So, when you break the IN clause up, you split the serial ORs up, and changed precedence.

gbn