I read the book - "SQL for mere mortals" - at the end of each chapter there is a task - to write short queries, so, I wrote a query to the task:

5.5. "Write down the request, which selects all orders, in which the amt (amount) field is set to O or NULL. The actual request:

SELECT * FROM Orders WHERE amt IN (0, 'NULL');

Correct request:

 SELECT * FROM Orders WHERE amt < > 0 AND (amt IS NOT NULL);

The question is why this type of query is not supported (if it is possible with explanations about data types)

  • Thank you, yes, I remember what was in the text. I reread. - aka86
  • one
    'NULL' is a string literal. this is already a mistake. Because 'NULL' <> NULL. len ('NULL') = 4, and len (NULL) = NULL. NULL is more than a value, it is a state. At attempt to integrate NULL in the list in - NULL will drop out from the list. Those. (0,NULL) equivalent to (0) and therefore meaningless. - nick_n_a

1 answer 1

It's all about the ternary logic. null != null => false and null = null => false . Therefore, to check for null , the is null construction is null used.