What is ternary (also known as) three-valued logic in SQL?
This is a question best illustrated by an example. Suppose we have the following SQL table with the columns modelNumber and laptopModel:
Computer {
modelNumber CHAR(30) NOT NULL,
laptopModel CHAR(15),
}
Assume that the table stores entries for all the makes of PC’s and laptops – and if it’s a laptop the laptopModel field is set. Given that information, let’s try to answer a question to explain three valued logic: How would you write a SQL statement that returns only the PC’s and no laptops from the table above?
You might think that the answer to this question is very easy, and the first thing that may come to mind is this answer:
SELECT * FROM Computer WHERE laptopModel = null
SQL uses Ternary/Three valued logic
Actually the SQL code above will not return anything at all – not even the PC’s that are actually in the table! The reason has to do with the fact that the fact that SQL uses ternary or three-valued logic. The concept of ternary logic is important to understand in order to write effective SQL queries.
SQL Logical Operations have 3 possible values
This is an important fact to remember: logical operations in SQL have 3 possible values NOT 2 possible values. What are those 3 possible values? They are TRUE, FALSE, and UNKNOWN. The UNKNOWN value, as it’s name suggests, simply means that a value is unknown or unrepresentable. Running the SQL code that we presented above will return UNKNOWN for a value.
The equality operator
The problem with the SQL statement above is the fact that we used the equality operator (the “=”) in order to test for a NULL column value. In the majority of databases, a comparison to NULL returns UNKNOWN – this is true even when comparing NULL to NULL. The correct way to check for a NULL or a non-NULL column is to use the IS NULL or the IS NOT NULL syntax. So, the SQL query should be changed to this:
SELECT * FROM Computer WHERE laptopModel IS NULL
This is a common mistake – so be sure to account for UNKNOWN values in WHERE clause conditions.