Can a WHERE clause predicate be evaluated to NULL?

Question

Can a WHERE clause return NULL instead of TRUE or FALSE? According to the exercise below, it’s possible, but I can’t imagine an example that returns NULL, is it really possible?

4. Which of the following values can NOT be returned after evaluation of WHERE clause condition? A. UNKNOWN B. TRUE C. FALSE D. NULL Answer: A. If the result of the condition in WHERE clause is not known, NULL is returned. In all other scenarios, either TRUE or FALSE is returned. 

Answer 1

In SQL, all logical operators evaluate to TRUE, FALSE, and UNKNOWN ( Oracle docs ) as a result of MySQL UNKNOWN calls NULL ( MySQL Docs ).

According to oracle documentation:

"To check for zeros, use only the comparison conditions IS NULL and IS NOT ZERO. If you use any other condition with zeros and the result depends on null, then the result is UNKNOWN."

Thus, after evaluation only TRUE, FALSE and UNKNOWN can be returned.

About your question:

"Can a WHERE clause return NULL instead of TRUE or FALSE?"

Strictly speaking, in Oracle - NO , because such a result is called UNKNOWN.

But overall, the meaning of UNKNOWN and NULL is equivalent in this context, and it's just a different name for the same thing. So the SQL example below ( aa >= all ) evaluates to UNKNOWN.

 with table_a as ( select null as a from dual union all select 10 as a from dual union all select 5 as a from dual), table_b as ( select null as a from dual union all select 10 as a from dual union all select 5 as a from dual) select * from table_a a where aa >= all(select a from table_b b) 

Answer 2

To explain the reason, keep in mind that SQL uses three-valued logic: TRUE , FALSE and NULL . Consider this order table,

If we run the following query, it will not return rows for CPU and Monitor

 SELECT * FROM Orders WHERE (qty < 1000 Or qty >= 1000) 

In this case, for the state of the CPU and monitor (qty < 1000 Or qty >= 1000) , neither TRUE nor FALSE returned. It returns NULL . Because logically this is unknown. So, the result of the condition in the WHERE unknown, and it returns NULL.

You can consider this link .

Answer 3

Even NULL can be equal to NULL.

• The right way to understand NULL is that it is not a value. Not "this is a NULL value", but "this NULL is not a value". Everything is either meaning or it is not.
• When something has a value, it is “1” or “hello” or “green” or “$ 5.00” etc. But when something does not matter, it is simply not anything at all.
• SQL represents "it doesn't matter" with the special non-value NULL value. When someone says "NULL", you need to mentally disagree, because theres no such thing. NULL - the complete, complete absence of any value. underlined text

Non-technical aspect

If you ask two girls how old are they? maybe you hear them refuse to answer your question, both girls give you NULL as age and this does not mean that both have the same age. So there is nothing equal to zero.

SELECT 0 IS NULL , 0 IS NOT NULL , '' IS NULL , '' IS NOT NULL, NULL != NULL, NULL = NULL, NULL != '', NULL = ''