Three-Valued Logic

Learned when I was learning about NULL values, and the truth value associated with them in SQL.

 In SQL, every arithmetic operation that includes an operand with a NULL value returns a NULL result.

https://learnsql.com/blog/understanding-use-null-sql/

3VL Queries and their results

Below outlines the different values you can obtain with different combinations of truth values (True, False and NULL). There are 3 possible outcomes: True, False or Unknown.

Important

Only records that evaluate to “true” in the WHERE clause are part of the query result set. Records evaluating to “false” or “unknown” are not part of the result.

Below is the zoomed table for the result of all operations for all three tables:

WHERE is shorthand for WHERE IS TRUE. We can introduce a special comparison IS NULL to select entries with NULL values.

List all author information (including null entries)

select * from author

List all author ids and names for which we don’t know a URL of their home page.

select aid, name from author \
where uri IS NULL

Aggregate Operations

For aggregate operations, NULL values do not “count” (i.e., “value inapplicable”).

Example

Consider the following example

If we run

SELECT * FROM employee WHERE bonus + salary > 1200;

We get

So, with this in mind, look at how Peter White’s record was evaluated:

1800 + NULL > 1200

In other words, Peter’s salary (1,800) and his bonus (NULL) added up to NULL. We can reduce this condition to :

NULL > 1200

What does this evaluate to? Unknown

Coping with the Unknown Variable

You can sometimes avoid using “unknown” values in WHERE conditions by converting NULL values to other values (like 0) using the COALESCE() function

For example,

SELECT * FROM employee WHERE coalesce(bonus,0) > 200 OR salary < 1500