Three-valued Logic¶

Virtual DataPort is fully conformant with the three-valued logic defined in the SQL standard.

The three-valued logic defines that a boolean expression can return one of these values:

True
False
Unknown, which is represented with the NULL value.

Consider the following rules when dealing with NULL values:

A comparison with a NULL value returns unknown.
A comparison with an unknown value returns unknown. That means that a function applied over a function that returns unknown, also returns unknown.
Conditions with unknown always returns false. That means that in queries with a WHERE and/or HAVING clause, only the rows for which the condition returns true are added to the result.
To evaluate if a value is or is not NULL, use the operators is null or is not null respectively. Do not use = null nor <> null because these will never return true.

Examples

Example 1

Let us say we execute this query:

SELECT *
FROM view_1
WHERE NOT (a = b)

Let us say that in one of the rows of “view_1”, a is NULL and b is 1. For this row, the result of (a = b) is unknown because a is NULL. The result of evaluating NOT (unknown) is also unknown. Therefore, this row is not added to the result of the query.

Example 2

SELECT *
FROM employee INNER JOIN department
ON employee.dept_id = department.id

This query does not return the employees whose column “dept_id” is NULL because a comparison with NULL returns unknown.

Example 3

Query to obtain all the customers whose state is not registered.

SELECT *
FROM customer
WHERE state is null

Note the use of the operator is null.

Example 4

Query to obtain all the customers whose state is registered.

SELECT *
FROM customer
WHERE state is not null

Note the use of the operator is not null.

Example 5

Example of query that does not return what you may expect.

SELECT *
FROM customer
WHERE state <> null

This query will always return 0 rows. The reason is that a comparison with a “null” value is never evaluated to true.

USER MANUALS

Three-valued Logic¶