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:

This can be pretty limiting however, and often we combine criteria:

We use the ‘and’ boolean join to chain conditions together. We can also use the ‘or’ operator.

Still pretty straightforward. But often our statements can get a bit more complex.

In this case, what is the intended behaviour? This statement could be intended to mean two things:

or

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.