CODEWITHSUNDEEP
sqloracle-databasenullwhere-clauseclause
Roni Castro
Roni Castro

Reputation: 2144

Can a WHERE clause predicate evaluate to NULL?

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.

Upvotes: 6

Views: 2498

Answers (3)

Aditya Shah
Aditya Shah

Reputation: 325

Not even a NULL can be equal to NULL.

  1. The correct 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 either is a value, or it isn’t.
  2. When something is a value, it is “1,” or “hello,” or “green,” or “$5.00″ etc – but when something isn’t a value, it just isn’t anything at all.
  3. SQL represents “this has no value” by the special non-value NULL. When someone says “the NULL value,” one should mentally disagree, because there’s no such thing. NULL is the complete, total absence of any value whatsoever. emphasized text

A Non-Technical aspect

If you ask two girls, how old they are? may be you would hear them to refuse to answer your question, Both girls are giving you NULL as age and this doesn't mean both have similar age. So there is nothing can be equal to null.

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

Upvotes: 0

Mikhailov Valentin
Mikhailov Valentin

Reputation: 1102

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)

Upvotes: 2

Md Rezowan Hossain Suvro
Md Rezowan Hossain Suvro

Reputation: 380

As to explain the reason, consider that the SQL language uses a three-value logic: TRUE, FALSE, and NULL. Let's consider this Orders table,

enter image description here

If we run the following query it wont return rows for CPU and Monitor

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

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

You can consider this reference.

Upvotes: 0

Related Questions