Database Related

In SQL, What Is Three-Valued (Ternary) Logic?

The answer of this question can be best explained with an example. Just suppose we have the following SQL table having the columns printerrModel and modelNumber:

 

Computer {

 

modelNumber CHAR(20) NOT NULL,

printerModelCHAR(10),

 

}

 

Now assume that the table stores entries for all the ‘makes’ of printers and PC’s – and in case, it’s a printer the printerModel field is set. With the available information, let’s try to answer a question for explaining that what a three valued logic is:

How will you write a SQL statement that returns just the PC’s and no printers from the above table?

You might be thinking that the answer of this question is very simple, and the first thing that may come to mind is the following answer:

 

SELECT * FROM Computer WHERE printerModel = null

 

  • Three valued logic/Ternary used by SQL

Basically, the above SQL code is not going to return anything at all – which also include the PC’s that are actually present in the table! The reason behind this is the fact that the SQL uses three-valued logic or ternary. The ternary logic concept is significant to understand for writing effective SQL queries.

  • SQL Logical Operations using 3 possible values

An important fact to remember is: There are 3 possible values in SQL logical operations NOT 2. But, the question arises, what are those 3 possible values? They are UNKNOWN, TRUE and FALSE. The UNKNOWN value, like its name shows, simply means that a value which is unrepresentable or unknown. If we will run the above presented SQL code, this will return UNKNOWN for a value.

  • The Equality Operator

The issue with the above SQL statement is the fact that we used the “=” (equality operator) in order to test a NULL column value. A comparison to NULL returns UNKNOWN in the majority of databases. This is correct even when matching NULL with NULL. The appropriate way to check for a non-NULL column or NULL is to use the IS NOT NULL or the IS NULL syntax. Thus, the SQL query must be changed to the following:

 

SELECT * FROM Computer WHERE printerModel IS NULL

 

Leave a Reply