Sunday, 18 February 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.