Comparing NULL's to Values The logic of conditions in SQL is really 3-valued logic:TRUE,FALSE, UNKNOWN. Comparing any value (including NULL itself)with NULL yields UNKNOWN
Comparing NULL ’ s to Values zThe logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN. zComparing any value (including NULL itself) with NULL yields UNKNOWN
Operation upon on NULL value When we operate on a NULL and any value,including another NULL, using an arithmetic operator like x or +the result is NULL. oA tuple is in a query answer iff the WHERE clause is TRUE (not FALSE or UNKNOWN)
Operation upon on NULL value zWhen we operate on a NULL and any value, including another NULL, using an arithmetic operator like x or +, the result is NULL. zA tuple is in a query answer iff the WHERE clause is TRUE (not FALSE or UNKNOWN)
Three-Valued Logic To understand how AND,OR,and NOT work in 3-valued logic,think of TRUE =1,FALSE 0,and UNKNOWN =1 /2. AND MIN;OR MAX,NOT(x)=1-x. Example: TRUE AND(FALSE OR NOT(UNKNOWN))=MIN(1,MAX(0,(1- /2)》= MIN(1,MAX0,2)》=MIN(1,2)③/2⊙
Three-Valued Logic zTo understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½. zAND = MIN; OR = MAX, NOT( x) = 1-x. zExample: TRUE AND (FALSE OR NOT(UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) = MIN(1, MAX(0, ½ )) = MIN(1, ½ ) = ½
Example bar beer price Joe's bar Bud NULL SELECT bar FROM Sells WHERE price 2.00 OR price >=2.00; UNKNOWN UNKNOWN UNKNOWN Joe's Bar is not produced,even though the wHERE condition is a tautology
Example bar beer price Joe's bar Bud N ULL SELECT bar FROM Sells W HERE price < 2.00 OR price >= 2.00; UNKN O W N UNKN O W N UNKN O W N z Joe's Bar is not produced, even though the WHERE condition is a tautology
Reason:2-Valued Laws !=3- Valued Laws Some common laws,like commutativity of AND,hold in 3- valued logic. But not others,e.g.,the law of the excluded middle p OR NOT p TRUE. When p=UNKNOWN,the left side is MAX(2,(1-2)=2!=1
Reason: 2-Valued Laws != 3- Valued Laws zSome common laws, like commutativity of AND, hold in 3- valued logic. zBut not others, e.g., the law of the excluded middle : p OR NOT p = TRUE. – When p = UNKNOWN, the left side is MAX( ½, (1 – ½ )) = ½ != 1