What is the three-valued logic of PostgreSQL?
In programming languages like C, Python, and JavaScript, logic can either be true or false. A simple comparison among two conditions can only yield one of these boolean values. However, there is a third possibility to a logical expression in SQL. In SQL, a logical expression can either be true, false, or unknown.
Where does the idea of the unknown come from?
Unknown essentially means that the logical expression is neither true nor false. Rather, it can be anything and is unknown.
Unknown data value originates from the availability of null in SQL, representing the absence of data in the database. A NULL value means that SQL does not know what the data is, and the logical operation cannot be true or false but unknown. The NULL value is used to represent the missing data in our database.
Note: Every arithmetic operation with one or more
NULLvalues returns an unknown result.
Code example
Let’s look at the following example of Null in PostgreSQL:
100 + NULL < 300
The output of the above comparison would be NULL because the logical operation is simply a comparison between NULL and 300, that is, NULL < 300. There is no way of knowing if the condition is right or wrong. So, we get an unknown output.
We can also analyze the three-valued logic in a coding example.
Logical operator with null operand in SQL
The difference of NULL in SQL compared to other languages promotes it from a binary logic language to a trinary logic language. We may run the following code as an example of logical operation:
CREATE TABLE Students (FirstName varchar(255),Stipend int,Expenses int);INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('George H.', 2400, 2000);INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Ben', NULL, 3200);INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Catherine', 3400, 2000);INSERT INTO Students (FirstName,Stipend,Expenses) VALUES ('Dough', 700, 2000);SELECT * FROM Students WHERE Stipend + Expenses > 3000;
The above code creates a table Students in PostgreSQL and inputs records in it. The lines 7 to 10 represent the insertion of data in the PostgreSQL database.
We want to know which student has a combined value of stipend and expenses greater than 3000. The following statement is used to perform the logical operation:
SELECT * FROM Students WHERE Stipend + Expenses > 3000;
If we use some language other than PostgreSQL or SQL, we'll also have Ben in the output. But instead, only Aaron and Catherine are shown in the output. So The actual operation performed by SQL is as follows:
NULL + 3200 > 3000NULL>3000>>NULL
Ben's stipend is NULL, which means PostgreSQL is comparing the sum of an unknown value and 3200 with 3000. This comparison would reduce to a comparison between an unknown value or a NULL and 3000. In this case, PostgreSQL would return a null value which essentially represents an unknown decision.
Free Resources
