From the course: Advanced SQL: Logical Query Processing, Part 1

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Dealing with ternary logic in SQL

Dealing with ternary logic in SQL - SQL Tutorial

From the course: Advanced SQL: Logical Query Processing, Part 1

Start my 1-month free trial

Dealing with ternary logic in SQL

“

- Most SQL developers struggle at first with ternary logic. To list all non-breed animals, we might be tempted to use a comparison predicate breed equals null. By now we know that every comparison operation involving nulls always evaluates to a null, so now we understand why these queries return an empty set, regardless if you use equality, non-equality, or any other mathematical comparison. Even a composite filter where a breed is equal to null or not equal to null returns an empty set. And intuitively it should return all animals, right? Even more confusing is that a predicate looking for all animals, which are a bull mastiff or all those that are not a bull mastiff doesn't return non-breed animals. All this is counter-intuitive for our humble, limited human brains. We expect all animals to be returned if they either equal something or not equal the same thing. But it doesn't work that way with ternary logic. Most of you…

Contents