0

Consider the following relation instance myTable

num1    num2  
100     100             
100     NULL       
NULL    100   
NULL    NULL

Query 1:

SELECT * 
FROM myTable 
WHERE num1 != NULL; 

Query 2:

SELECT * 
FROM myTable 
WHERE num1 = NULL;

I know that NULL values are looked up using IS NULL and IS NOT NULL. But I want to know how are the above two queries evaluated. What is the number of tuples outputted by Q1 and Q2 by the ANSI standards?

  • Any operation (comparing, calculation, concatenation, etc.) with NULL as one or more operand(s) except IS [NOT] NULL and NULL-safe compare gives NULL. In a condition the result of NULL is treated as FALSE. – Akina Aug 2 '18 at 17:45
4

NULL is the special value that need some special handling. Therefore there is three (at least) NULL-safe operators in the SQL:

  • x IS NULL
  • x IS NOT NULL
  • x <=> NULL

The third one <=> is the same as = but NULL-safe, because = behave improperly with NULLs.

mysql> SELECT 1 = NULL;
+----------+
| 1 = NULL |
+----------+
|     NULL |
+----------+

mysql> SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

And now the NULL-safe version:

mysql> SELECT 1 <=> NULL;
+------------+
| 1 <=> NULL |
+------------+
|          0 |
+------------+

mysql> SELECT NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+

In your case WHERE clause will get NULLs instead of true or false for each row and the result is an empty set in the both cases.

4

NULL means that there is no value for that column of the record and it does not equal anything. So in both of your queries nothing can equal NULL.

Wiki Entry on Null

The Greek lowercase omega (ω) character is used to represent Null in database theory. Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.