SQL Structure – Where And Or

In Technical by jayden.macrae

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.