SQL: AND vs OR (operators)

Tweet

In SQL Server we can use different operators in our queries: AND, OR, LIKE, BETWEEN and so on. In this short post we are going to see AND and OR because I've noticed that some could get confused by the results obtained when using them.

So, let's clear the situation a bit, because we really need to know what we are doing!

General
Just to be clear, there's a big difference between AND and OR, in fact they both return a Boolean data type with a value of TRUE or FALSE, but:
AND returns TRUE when both Boolean expressions are TRUE
OR returns TRUE when either Boolean expressions are TRUE
That might sound confusing, but please take note of the difference above as a start.

AND
The operator combines two Boolean expressions and returns TRUE when both are TRUE. The operator is always evaluated first: so when we use different operators, AND comes always first. That's important, because if we use an AND operator together, for example, with an OR operator, AND is always evaluated first. To change that, we need to use parentheses.

In a query when we search records, we use AND when we want both expressions to be true. However we should consider the unexpected. Follow me...
The result of two TRUE expressions is TRUE. When just one of the two expressions is FALSE, the result is FALSE. When one expression is UNKNOWN: if the second is TRUE we get UNKNOWN; if the second is FALSE we get FALSE; if the second is UNKNOWN we get UNKNOWN.

Ok, I know it's complicated, but please just spend a few minutes to understand the above because it will save you a lot of time when an operator is not giving the expected result.

OR
The OR operator just combines two Boolean operators. It doesn't matter if just one of the two expressions is TRUE. As said, when we use the OR operator together with an AND operator, the AND operator is evaluated first.
Again, let's see the results when we use the OR operator.
The result of two TRUE expressions is TRUE. That is even if just one expression is TRUE:
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
FALSE OR TRUE = TRUE
Obviously two FALSE expressions will give us FALSE. If one expression is UNKNOWN, and the second is TRUE, we get TRUE; if the second is FALSE, we get UNKNOWN; and if the second is UNKNOWN we get UNKNOWN.

Please try again to understand the above, because it will really help you when things get confused.

Ok folks, that's all for today. See you next time...