T-SQL 101: #23 Combining multiple WHERE clause predicates with AND, OR, and NOT in SQL Server

WHERE clauses can have more than one predicate.

In the case shown above, I'm saying

"where the description starts with hydro and not size equals 370ml"

The operators AND, OR, and NOT can be used to form a group of logical decisions that need to be made.

Now notice that in this case, it's exactly the same as if I had said

"where the description starts with hydro and size is not equal to 370ml"

In the original query though, it becomes little more interesting if we add another option so that the size is 370ml or 600ml.

The question is how does this work. Is it a question of having Hydro and 370ml OR just 600ml on its own, or is it a question of having Hydro and either 370ml or 600ml?

The answer is that languages like SQL have precedence rules but I'd suggest you should never depend upon them. Instead, use parentheses to show what you mean, like this:

So in this case, I'm saying that the description must start with Hydro and either size is OK. But I've made it clear the grouping of how those clauses go together.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2 thoughts on “T-SQL 101: #23 Combining multiple WHERE clause predicates with AND, OR, and NOT in SQL Server”

  1. I’m glad that somebody else agrees with me on using parentheses to explicitly express the intent of compound filters. Yes, SQL Server has predictable precedence rules, but (1.) I don’t always remember them, and (2.) more importantly, using parens is much more readable and clearer.

    1. Yep, clarity is the most important thing there. I know the precedence rules but I don't want to assume that someone reading the code is right across them.

Leave a Reply

Your email address will not be published.