CODEWITHSUNDEEP
sqlsql-server-2008
AYA
AYA

Reputation: 11

What is the difference between AND OR operators in the brackets and without?

I am trying to better understand how SQL reads AND/OR Operators depending on whether I use brackets or not.

For example:

Select *
From Table1
Where (C1=ProductA or C1=ProductB) AND C3>20150101

Select *
From Table1
Where C1=ProductA or C1=ProductB AND C3>20150101

In the first example the query will be read correctly - I will only select Product A and B from all products with a release date higher than 1st of January,2015. However,in the second query I will have 20 extra records where release date would be before 2015,meaning that the third condition is not evaluated if any of the first conditions are correct. Am I understanding it right?

If this is the case I should ALWAYS use brackets around my OR statements when combined with AND, correct?

Thanks in advance.

Most links I found explained the use or and or operators, but did not elaborate on how they can be used when combined. http://www.tutorialspoint.com/sql/sql-and-or-clauses.htm http://www.techonthenet.com/sql/and_or.php

Upvotes: 1

Views: 980

Answers (1)

Luaan
Luaan

Reputation: 63722

The point is to always make it obvious what you mean. When you say

(A or B) and C

It's immediately obvious that you want something that's C, plus (non-exclusively) either A or B.

When you say

A or B and C

It's not clear whether you're saying (A or B) and C, or A or (B and C).

In general, it's a good idea to maintain a hierarchy - it's fine to omit parentheses when you're dealing with the same level, e.g.:

A and B and C

But it's very confusing when they're not, e.g.:

A or B and C or D

Even if you understand the operator precedence well, the intent is not clear. In a simple example like this, any human would assume you mean (A or B) and (C or D), but SQL instead treats it as A or (B and C) or D.

In T-SQL, and has precedence over or. So your expression turns out to be A or (B and C) - obviously not what you wanted. Just use parentheses whenever you're combining different levels (treat the A or B as a separate expression), and you'll avoid a lot of trouble.

Don't think about operator precedence. Think about intent - that will make it obvious where parentheses make sense, and as a side-effect, work properly :)

Upvotes: 3

Related Questions