Lecture 3 - 3-valued SQL and NULL

Question 1

What is the three-valued logic of SQL?

Answer 1

TRUE (1), FALSE (0) and UNKNOWN(0.5)

Question 2

What are the principles of NULL values?

Answer 2

• That each null value is different from another
• any value compared to NULL is null

Question 3

What do we need to adopt to compare to NULL?

Answer 3

IS / IS NOT NULL

Question 4

min / or with UNKNOWN?

Answer 4

Will evaluate to UNKNOWN unless there is a false and unknown combination

Question 5

max / and with UNKNOWN?

Answer 5

TRUE WITH UNKNOWN IS TRUE OTHERWISE UNKNOWN

Question 6

difference / not with NULL?

Answer 6

TRUE = FALSE
FALSE = TRUE
UNKNOWN = UNKNOWN

Question 7

Why won’t this work:

COMPARISON INVOLVING NULL
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn = NULL

Answer 7

As Super_ssn = NULL will always evaluate to unknown, which will false when we take min

Question 8

Why won’t the following work?

SELECT *
FROM EMPLOYEE
WHERE DNO NOT IN (1, 2, NULL)

Answer 8

This won’t work as we are not checking for NULL with the IS statement, because of this the boolean will result in UKNOWN, which when the min / and is taken we get UNKNOWN (i.e. when DNO = 3).