Fix WHERE clause AND/OR/NOT confusion with truth tables

Today on the #sqlhelp hashtag, I found this outstanding question about logic in the WHERE clause, and pounced on it:

#sqlhelp pls explain why WHERE NOT (O.StatusId = 1 AND O.UserForeignId = 'TD75R') &
WHERE (O.StatusId <> 1 AND O.UserForeignId <> 'TD75R') gives different results? #confusion

— Simon Richardson🇿🇼 🏴󠁧󠁢󠁥󠁮󠁧󠁿 (@TSQL_Simon) April 17, 2018

This is not at all unusual, and I find that my early love of algebra (and brief stuies in circuitry logic) really comes in handy for working these things out.

It certainly seems like these two options should be equivalent, right? WHERE NOT (“and-ed” truth) should be equivalent to WHERE (opposite of that truth).

Shouldn’t this…	…be the same as this?	(Answer)
WHERE NOT (Id = 1 AND ForeignId  = ‘TD75R’)	WHERE NOT ID = 1 AND NOT ForeignID = TD75R	No, actually.
WHERE (Id <> 1 AND  ForeignId  <> ‘TD75R’)	WHERE ID <> 1 AND ForeignID <> TD75R	Yes, actually!

 

The problem comes in, oddly enough, where we kind of assume that the NOT can be multiplied against each of the inner items, just like x(4 + 2) = 4x + 2x. That’s the distributive property of algebra, by the way.

It can’t. Logic gates work slightly different than plain-Jane algebra, and “NOT” is a weird force anyway. Let’s work all this out by hand, using a truth table.

How to work out WHERE clause logic manually

It’s really fine if all the circuitry and algebra stuff makes no sense to you. We’re going to use a tried-and-true method to figuring out how these things will come out.

For a truth table, you just put in every combination of input types – meaning, inputs that will evaluate to true, and those that evaluate to false – and work out how the clause will evaluate it overall.

What do I mean? Well, we have two inputs for the questions above: StatusID and UserForeignID, which I’ll shorten to ID and ForeignID to save characters. Logically speaking:

• ID can either be equal to 1, or to a value other than 1.
• ForeignID can either be equal to TD75R, or to a value other than TD75R.

So the possible scenarios are as follows:

ID	ForeignID	In English…
1	TD75R	Both values are what we’re looking for.
1	Some value that’s not TD75R, like “X”	Only one value is what we’re looking for.
2	TD75R	Only one value – the other one – is what we’re looking for.
2	Some value that’s not TD75R, like “X”	Neither value is what we’re looking for.

 

Now, we take that table, and evaluate the outcome for each of the two different clauses. Remember two things. One, that SQL Server evaluates what’s in the parentheses first! And two, that “true” ends up meaning “yes, I would like this row returned”, and “false” means the row is excluded from the result set.

Truth table for “WHERE NOT (Id = 1 AND ForeignId  = ‘TD75R’)”

Here are the inputs, work shown, and result from the clause WHERE NOT (Id = 1 AND ForeignId  = ‘TD75R’):

ID	ForeignID	Show your work	Result
1	TD75R	WHERE NOT (True AND True) = WHERE NOT (True) ==	False
1	Some value that’s not TD75R, like “X”	WHERE NOT (True AND False) = WHERE NOT (False) ==	True
2	TD75R	WHERE NOT (False AND True) = WHERE NOT (False) ==	True
2	Some value that’s not TD75R, like “X”	WHERE NOT (False AND False) = WHERE NOT (False) ==	True

 

Truth table for “WHERE (Id <> 1 AND  ForeignId  <> ‘TD75R’)”

Here are the inputs, work shown, and result from the clause WHERE (Id <> 1 AND  ForeignId  <> ‘TD75R’):

ID	ForeignID	Show your work	Result
1	TD75R	WHERE (False AND False) = WHERE (False) ==	False
1	Some value that’s not TD75R, like “X”	WHERE (False AND True) = WHERE (False) ==	False
2	TD75R	WHERE (True AND False) = WHERE (False) ==	False
2	Some value that’s not TD75R, like “X”	WHERE (True AND True) = WHERE (True) ==	True

 

Results

When we compare the two truth tables, we see that the same inputs produce entirely different results! Those two clauses are not equivalent.

Technically, what’s the difference?

The answer, in case you’re curious, is that the first clause – the WHERE NOT clause – is officially a NAND gate. It literally takes the result of the inner outputs (which are AND-ed, by the way)…and reverses them. So the only possible way to get the result “False”, is if all the inputs are “True”.

The second clause is NOT a NAND gate. It’s just a regular AND gate, where the inputs happen to have a “not equals” as the deciding function.

Make sense?

No?

Then stick to the truth tables, like I do.

The problem gets bigger with more ANDs and ORs

Just be sure you get EVERY POSSIBLE LOGICAL COMBINATION (though, not every possible input…that’d be silly and impossible), meaning every combination of “evaluates to true” and “evaluates to false” for each and every one of your inputs. Note that this gets more complex as the inputs increase. For example, if the clause above had been “WHERE NOT (Id = 1 AND ForeignId  = ‘TD75R’ AND val=20)”

So the possible scenarios are as follows:

ID	ForeignID	val	In English…
1	TD75R	20	All values are what we’re looking for.
1	Some value that’s not TD75R, like “X”	20	ForeignID is the only value we’re not looking for.
1	TD75R	5	Val is the only value we’re not looking for.
1	Some value that’s not TD75R, like “X”	5	ForeignID and val have values we’re not looking for.
2	TD75R	20	ID is the only value we’re not looking for.
2	Some value that’s not TD75R, like “X”	20	ID and ForeignID have values we’re not looking for.
2	TD75R	5	ID and Val have values we’re not looking for.
2	Some value that’s not TD75R, like “X”	5	Nothing has the value we’re looking for.

 

And then we’d have to work out the results (always showing our work!) for each scenario.

That’s enough for today. I hope at the very least, you’ve learned how to use truth tables to work out results for complicated clauses!

 

P.S. This is a huge topic, and I can’t even begin to cover it all in one blog post. For one thing, AND/OR/NOT logic applies elsewhere – IF, CASE, JOIN…ON, and so forth. For another, there’s a lot more theory here. Feel free to chime in with more info and more references if you’ve got em!