CODEWITHSUNDEEP
sqlsql-serverdatabase
Ketchup
Ketchup

Reputation: 3111

SQL Order of precendence of query expressions

I am trying to work out how SQL queries are run and have hit a bit of a stumbling block.

If a where clause akin to the below is used:

A OR B AND C

This could mean either of the below

(A OR B) AND C

or

A OR (B AND C)

In the majority of cases the results will be the same, but if the set to be queried contains solely {A}, the first variant would return an empty result set and the second would return {A}. SQL does in fact return the 1 result.

Does anyone know (or have links to) any insight that will help me understand how queries are built?

Ketchup

Upvotes: 1

Views: 112

Answers (4)

onedaywhen
onedaywhen

Reputation: 57093

In the knowledge (from documentation) that AND has a higer precedence than OR, you should aim to write predicates for WHERE clauses in conjunctive normal form ("a seires of AND clauses").

If the intention is 

( A OR B ) AND C

then write it thus and all is good.

However, if the intention is 

A OR ( B AND C )

then I suggest you apply the distributive rewrite law that results in conjunctive normal form i.e. 

( P AND Q ) OR R   <=>  ( P OR R ) AND ( Q OR R )

In your case:

A OR ( B AND C ) <=>  ( A OR B ) AND ( A OR C )

Upvotes: 2

riffnl
riffnl

Reputation: 3183

For SQL-Server (which is your tag) here is the precedence http://msdn.microsoft.com/en-us/library/ms190276.aspx but.. If you're worried about the exact result set given you should indeed start working with () subsets.

Upvotes: 0

juergen d
juergen d

Reputation: 204924

AND and OR have different precedende.

See Precedence Level

Upvotes: 0

ᴘᴀɴᴀʏɪᴏᴛɪs
ᴘᴀɴᴀʏɪᴏᴛɪs

Reputation: 7529

The order is the following according to MSDN:

  1. ~ (Bitwise NOT)

  2. (*) (Multiply), / (Division), % (Modulo)

  3. (+) (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)

  4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

  5. NOT

  6. AND

  7. ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

  8. = (Assignment)

Upvotes: 2

Related Questions