How is where clause in SQL evaluated?

Question

I have thought of this a lot and never found the answer. I have found my self in situations where i haven't known how the conditions in a where clause of a SQL query is evaluated. To be sure, i have always used parentheses to make it more readable, and simple to understand. I consider it to be good practice to use parentheses sometimes to increase readability, even if it maybe isn't needed.

Consider the following query

SELECT * 
FROM mytable 
WHERE name='Anton' 
  AND lastname='Gildebrand' 
   OR age > 18 
  AND country='Sweden'

I imagine that this query is actually equal to

SELECT * 
FROM mytable 
WHERE name='Anton' 
 AND (lastname='Gildebrand' OR (age > 18 AND country='Sweden'))

Is this correct? Or how is the conditions evaluated? Does it differ from different database engines?

And this is why use of `OR` should also mean use of parenthesis; just for the readability. — Clockwork-Muse, Mar 01 '13 at 21:36

MarcinJuraszek · Accepted Answer · 2013-03-01T21:38:36.473

8

SELECT * FROM mytable
WHERE name='Anton' AND lastname='Gildebrand' OR age > 18 AND country='Sweden'

Is equal to:

SELECT * FROM mytable
WHERE (name='Anton' AND lastname='Gildebrand') OR (age > 18 AND country='Sweden')

Sources: TSQL, MySQL, Oracle, PostgreSQL

edited Mar 01 '13 at 21:38

answered Mar 01 '13 at 21:30

MarcinJuraszek

121,297
15
183
252

Are you ***sure*** about this? [Operator precedence](http://dev.mysql.com/doc/refman/5.6/en/operator-precedence.html) is `AND` then `OR` – Kermit Mar 01 '13 at 21:32
@AarolamaBluenk: yes, `AND` is evaluated before `OR`. I updated the answer with some links. – MarcinJuraszek Mar 01 '13 at 21:37
@AarolamaBluenk . . . Double yes. This is explicit in the ANSI standard, as well as the convention in many programming languages. – Gordon Linoff Mar 01 '13 at 21:37
Cool, didn't think of that one. Everyone seems to agree with your answer so i will accept it as answer :) – Anton Gildebrand Mar 01 '13 at 22:05

PM 77-1 · Answer 2 · 2013-03-06T19:27:00.173

3

If you are talking about and and or, then in absence of parenthesis and is evaluated first. The same as * and + in math.

These rules are not dependent on SQL implementations.

edited Mar 06 '13 at 19:27

answered Mar 01 '13 at 21:32

PM 77-1

12,254
20
64
106

1

That link, by the way, makes no mention at all of operator precedence, that I can see. A critical failure of that documentation, IMO. (and pretty much par for the course with w3schools.) Your answer is correct, though. – Andrew Barber Mar 02 '13 at 08:05
I agree with @Andrew, this answer is correct but the link is pretty much useless. – ypercubeᵀᴹ Mar 06 '13 at 19:10
@ypercube - removed the link – PM 77-1 Mar 06 '13 at 19:27

How is where clause in SQL evaluated?

2 Answers2

Linked