Example of Three Valued Logic in SQL Server

Example of three valued logic in SQL Server

An example of TRUE || NULL = True would be

declare @x as int = null;
if 1=1 or @x/1=1
    print 'true'

An example of FALSE && NULL = False would be

declare @x as int = null;
if not(1=2 and @x/1=1)
    print 'false'

How to apply 3-valued-logic to SQL queries?

I will focus on the concrete example, which is more proper for clarifying things.
Put simply, your logical expression is made of a conjunction of three clauses

C1: phone = '141-3304913'
C2: age < 50
C3: age >= 50

for which tri-boolean logic states that the result is

True, if any clause is true
False, if all clauses are false
Unknown, in all the other cases

Consequently, if the value associated with True is the largest, with False is the smallest, and with Unknown is any intermediate value, then taking the MAX for a conjunction proves correct. Similarly, a disjunction works with the MIN function. Negation works as long as we interpret any value between 0 and 1 (excluded) as Unknown; clearly, if we take 1/2 then the negation function is "stable", but that does not really matter in mathematical terms.

More operatively, the clauses clearly react to the following values (instances) of your phone variable P and your age variable A:

P1 such that P1 = '141-3304913'
P2 such that P2 <> '141-3304913'
P3 such that P3 = NULL
A1 such that A1 < 50
A2 such that A2 >= 50
A3 such that A3 = NULL

In terms of satisfaction of the clauses, we have

P1 -> C1 = 1
P2 -> C1 = 0
P3 -> C1 = 1/2
A1 -> C2 = 1, C3 = 0
A2 -> C2 = 0, C3 = 1
A3 -> C2 = C3 = 1/2

In general there exist 3*3 possible combinations, since each of your two variables takes three possible values:

P1 A1: C1 = 1, C2 = 1, C3 = 0 -> MAX(1,1,0) = 1 -> true
P1 A2: C1 = 1, C2 = 0, C3 = 1 -> MAX(1,0,1) = 1 -> true
P1 A3: C1 = 1, C2 = 1/2, C3 = 1/2 -> MAX(1,1/2,1/2) = 1 -> true
P2 A1: C1 = 0, C2 = 1, C3 = 0 -> MAX(0,1,0) = 1 -> true
P2 A2: C1 = 0, C2 = 0, C3 = 1 -> MAX(0,0,1) = 1 -> true
P2 A3: C1 = 0, C2 = 1/2, C3 = 1/2 -> MAX(0,1/2,1/2) = 1/2 -> unknown
P3 A1: C1 = 1/2, C2 = 1, C3 = 0 -> MAX(1/2,1,0) = 1 -> true
P3 A2: C1 = 1/2, C2 = 0, C3 = 1 -> MAX(1/2,0,1) = 1 -> true
P3 A3: C1 = 1/2, C2 = 1/2, C3 = 1/2 -> MAX(1/2,1/2,1/2) = 1/2 -> unknown

In particular, since C2 and C3 are mutually exclusive, you never get False as a result of the conjunction.

The expression R.a > R.b OR R.a <= 0 OR R.b >= 0 instead presents these cases:

R.a <= 0, R.a > 0, R.a = unknown
R.b >= 0, R.b < 0, R.b = unknown
R.a - R.b > 0, R.a - R.b <= 0, R.a - R.b = unknown

Apparently we have three variables and 27 possible cases, but several related to R.a - R.b can be trivially ruled out.

In SQL Server, how can I use the result of a comparison operator as a logical value?

SQL Server doesn't have a boolean type. Perhaps you intend:

select (case when a > 2 and b > 2 and (c > 2 or c is null)
             then 1 else 0
        end) as is_reject

Null values - boolean expression

You need three-phase truth tables for boolean AND and OR:

OPERAND1  OPERATOR  OPERAND2  RESULT
------------------------------------
TRUE      AND       TRUE      TRUE
TRUE      AND       FALSE     FALSE
FALSE     AND       FALSE     FALSE
TRUE      AND       NULL      UNKNOWN
FALSE     AND       NULL      FALSE
NULL      AND       NULL      UNKNOWN
TRUE      OR        TRUE      TRUE
TRUE      OR        FALSE     TRUE
FALSE     OR        FALSE     FALSE
TRUE      OR        NULL      TRUE
FALSE     OR        NULL      UNKNOWN
NULL      OR        NULL      UNKNOWN

This table is abbreviated, relying on the commutative properties of AND and OR in Boolean logic. In most SQL variants, UNKNOWN and NULL are equivalent. In plain English, if the value can be known even with partial information, it is provided. For example X OR Y is always true if X is true, whether or not Y is known and regardless of the value it takes. Similarly, X AND Y is always false if X is false, whether or not Y is known and regardless of the value it takes. These are lemmas on the Boolean operators and are taught in introductory set theory, among other places.

Once you have these truth tables, you can evaluate your expressions, following the proper precedence order for SQL operators when parentheses are not present.

Here is an evaluation chain for one of your examples:

• (NULL AND TRUE) OR FALSE
• (NULL) OR FALSE
• NULL OR FALSE
• NULL

See Wikipedia's Three-valued logic entry:Application in SQL for more.

One final note: NULL is never equal to NULL, because you cannot know if two unknown values are in fact equal to each other.

---

Related Topics