Advanced Database Management System - Tutorials and Notes: How does SQL evaluate conditions for tuples (records) containing NULLS?

Sunday, 18 February 2018

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

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

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents