SQLTeam.com | Weblogs | Forums

Operator Precedence in SQL: And and Or

Are the following two statements interchangeable?

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

I used this article as a resource. Is there a truth table I can use to check this?

i did some testing to find out

as you can see the results are not the same


AND takes percedence over OR

its doing this some_col in (4,5) and age > 20 which 4,5
its doing this some_col in (1,2,3) which 1,2,3

what if you have .. col in and age > 20 OR name = 'x' OR fgh =9 AND sdf = 'OP'
how will it do ?


create sample data script

declare @temp table(some_col int , age int )

insert into @temp select 1,40
insert into @temp select 2,30
insert into @temp select 3,15
insert into @temp select 4,34
insert into @temp select 5,60

select * from @temp

select 'data ',* from @temp

select 'in 12345 and some', * from @temp 
where some_col in (1,2,3,4,5) and age > 20 

select 'in (123) or some_col in(4,5) and some',* from @temp 
where some_col in (1,2,3) or some_col in (4,5) and age > 20

3 Likes

No. What you would need is:

WHERE ( some_col in (1,2,3) or some_col in (4,5) ) AND some_other_expr

Or:

WHERE some_col in (1,2,3) or ( some_col in (4,5) AND some_other_expr )

2 Likes

Thanks for helping me out.