6

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.
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Roni Castro
  • 1,968
  • 21
  • 40

3 Answers3

2

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)
Mikhailov Valentin
  • 1,092
  • 3
  • 16
  • 23
0

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.

Suvro
  • 352
  • 2
  • 13
  • Standard SQL describes a three-valued logic where the possible values are `TRUE`, `FALSE` and `UNKNOWN`. It's only mysql, so far as I'm aware, the confuses/conflates `UNKNOWN` and `NULL`. – Damien_The_Unbeliever Jan 07 '16 at 07:16
  • @Damien_The_Unbeliever No, MS SQL does the same thing as well. And since `UNKNOWN` and `NULL` are supposed to behave exactly the same, I don't really see why you'd want two keywords for the same thing. In any case, you are right that ANSI SQL has both `UNKNOWN` and `NULL`, for whatever reason. – Luaan Jan 07 '16 at 07:27
  • @Luaan - not according to [their documentation](https://msdn.microsoft.com/en-GB/library/ms188074.aspx): "The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN" – Damien_The_Unbeliever Jan 07 '16 at 07:31
  • @Damien_The_Unbeliever Interesting. They hide it rather well - there is no `unknown` keyword, but `(null = null) is null` is a syntax error (since, as you noted, boolean cannot have a value of `null`). I guess the distinction isn't readily apparent since MS SQL doesn't really have a "public" bool type - only boolean expressions have a type of bool, you can't have bool literals, and you can't have bool variables or columns. – Luaan Jan 07 '16 at 09:36
0

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 = ''

Aditya Shah
  • 325
  • 2
  • 13