How does SQL evaluate conditions for tuples (records) containing NULLS?

Sunday, February 18, 2018

How does SQL evaluate conditions for tuples (records) containing NULLS?

Question:

For a tuple {‘C110’, ‘Abhishek’, NULL, ‘Mumbai’, 7867564534}, what will be the result of the following WHERE clauses? Choose the appropriate from TRUE, FALSE, and UNKNOWN.

1. WHERE Age > 18

2. WHERE Age > 18 OR Name = 'Abhishek'

3. WHERE Age > 18 OR NOT (Age > 18)

4. WHERE Phone = 7867564534 AND Age = ‘NULL’

5. WHERE Age is NULL

Answer:

SQL essentially uses 3-valued logic, where comparisons involving NULLs evaluate to a third value which is UNKNOWN.

1. WHERE Age > 18 – UNKNOWN

Why? Age is NULL. NULL compared with any value is UNKNOWN.

2. WHERE Age > 18 OR Name = ‘Abhishek’ – TRUE

Why? Age is NULL. But Age > 18 is not the only condition. It is ORed with the condition Name = ‘Abhishek’ results in TRUE. For the condition involving logical operator OR, if any one of the conditions return TRUE then the answer becomes TRUE. Hence the answer is TRUE.

3. WHERE Age > 18 OR NOT (Age > 18) – UNKNOWN

Why? Both conditions involve Age attribute and Age is NULL. Hence the answer is UNKNOWN.

4. WHERE Phone = 7867564534 AND Age = ‘NULL’ – UNKNOWN

Why? In the condition Age = ‘NULL’, yet the value of Age is NULL. Hence the result is UNKNOWN.

5. WHERE Age is NULL – TRUE

Why? In SQL, an tuple with NULL value for an attribute can be identified using the syntax ‘attribute IS NULL’. If the value of that particular attribute is NULL, then the result is TRUE.