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.

**************

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

NULL value logic

Three valued logic

How NULL values are evaluated in SQL ?

NULL values are evaluated to TRUE, FALSE, UNKNOWN

## No comments:

## Post a Comment