Can a WHERE clause predicate evaluate to NULL?

Question

Can a WHERE clause return NULL instead of TRUE or FALSE? According to the exercise below it is 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) in MySQL UNKNOWN result calls NULL (MySQL docs).

According to oracle documentation:

"To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN."

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

About your question:

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

Strictly speaking in Oracle - NO because the such result called UNKNOWN.

But in general the meaning of UNKNOWN and NULL is equivalent in this context and it is just a different name for the same thing. So the example of SQL below (a.a >= all) evaluated as 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 a.a >= all(select a from table_b b)