In the last blog post, we looked at the basics of formatting complex SQL statements.
We will often use a Where clause to limit the records we return in a statement.
The basic structure of a Where clause is:
where field1 = value1
This can be pretty limiting however, and often we combine criteria:
where field1 = value1 and field2 = value2
We use the ‘and’ boolean join to chain conditions together. We can also use the ‘or’ operator.
where field1 = value1 or field2 = value2
Still pretty straightforward. But often our statements can get a bit more complex.
where field1 = value1 or field2 = value2 and field3 = value3
In this case, what is the intended behaviour? This statement could be intended to mean two things:
where
field1 = value1
or
(
field2 = value2
and
field3 = value3
)
or
where
(
field1 = value1
or
field2 = value2
)
and
field3 = value3
If you are a smarty pants, you may know the order of precedence that the database engine will apply the operators. For MS SQL Server see the Operator Precedence information. We can see that AND is applied before OR. This means that SQL Server will evaluate ANDs before the ORs.
This results in an evaluation where the final two conditions are evaluated first, and then ‘ored’ with the first condition. In my opinion you are far better to take the time however, and be explicit and show your intention by using brackets and indenting. There is then no ambiguity as to your intent and another person is not left reading your code wondering if you understood the order of precedent or just blindly used a number of conditions with operators with different precedents.