Why SQL does finally treat a NULL value as FALSE?

Question

Since NULL it's just an unknown or missing value, so maybe it's TRUE but we don't know. Why FALSE presumption? Does it have any justification besides "it's obvious" (since it is not) or should be considered a kind of SQL bad design artifact?

for ex.:

SELECT * FROM `rainbow_table` WHERE `show_me`

and some rows have null show_me. We really don't know if we should output such rows, maybe it is better to show it (as the last chance to prevent data loss)? It seems like SQL was developed by pessimists.

you may get some clarification regarding ur question here ... See below link : [http://programmers.stackexchange.com/questions/133600/should-i-store-false-as-null-in-a-boolean-database-field] — User Learning, Jun 25 '15 at 03:10
I think great answers and workarounds to your question are here http://stackoverflow.com/q/1843451/4155741 where can i find more of your duck test jokes?. — konzo, Jun 25 '15 at 03:19
It doesn't treat `NULL` as false. This is trivially demonstrated by putting `NOT` in front of an expression and discovering that it's not treated as true. — Damien_The_Unbeliever, Jun 25 '15 at 06:25
@a_horse_with_no_name's tag edit has changed this from what I perceived to be a general/standard [tag:sql] (albeit one with some mysql syntax included) to being a more specific [tag:mysql] one. If it's your intention to ask specifically about mysql, let me know and I'll delete my answer. Otherwise, I'd suggest rolling back the tag edit and perhaps making clear that you want to discuss the SQL language rather than mysql specifically. — Damien_The_Unbeliever, Jun 25 '15 at 07:50
@Damien_The_Unbeliever: mikhail is apparently using MySQL so I added the mysql tag just in case there are some gotchas in MySQL regarding nulls (or boolean values which MySQL doesn't have and simulates in a non-standard way). I think the combination of `sql` **and** `mysql` should indicate that SQL "only" answers are just as fine btw: your answer does not prove anything for MySQL as it doesn't support check constraints - but it _is_ a nice example where null means "not false" (rather than "true") — , Jun 25 '15 at 08:11

score 2 · Answer 1 · answered Jun 25 '15 at 02:55

2

SQL does not really treat NULL as false. Instead, conditional statements are considered true only when the condition evaluates to true.

The effect is that NULL is treated as false. But this does not mean that NULL is equivalent to false.

answered Jun 25 '15 at 02:55

Gordon Linoff

1,242,037
58
646
786

My question is exactly about why NULL is treated as false at the end. Therefore NULL is a kind of FALSE. We have one TRUE and two FALSEs. Duck test - "If it looks like a duck, swims like a duck, and quacks like a duck, then it probably is a duck." – Mikhail Karakulov Jun 25 '15 at 03:03
1

but !NULL is not true. – 1010 Jun 25 '15 at 03:17
1

@MikhailKarakulov . . . It is not that `NULL` is treated as false. It is that `TRUE` is true and nothing else (to a close approximation). In particular, `NULL` is not `TRUE`, so it fails all conditionals except `IS NULL` (and perhaps some extensions in particular languages). Don't focus on `NULL`, focus on `TRUE`. – Gordon Linoff Jun 25 '15 at 22:06

Damien_The_Unbeliever · Accepted Answer · 2015-06-25T06:41:56.427

Within the context of a SELECT statement (so, in ON clauses, the WHERE clause, and within CASE expressions), predicates must be TRUE (not FALSE or UNKNOWN¹) for the predicate to be satisfied.

However, within CHECK constraints, predicates must not be FALSE in order to be satisfied.

I.e. the following script will work:

CREATE TABLE T (
  ID int not null,
  Val varchar(10) null,
  constraint CK_Vals CHECK (Val in ('abc','def'))
);
INSERT INTO T(ID,Val) VALUES (10,NULL);

So we can see that it is not universally true in SQL that UNKNOWN results are treated as FALSE. It's also trivially demonstrated by the fact that wrapping a predicate that produces UNKNOWN with NOT (<existing predicate>) does not produce TRUE.

The wikipedia page on Three-Valued logic covers a lot of details.

¹I'm assuming your question is about UNKNOWN rather than NULL, since you've tagged sql and relational-algebra. In standard SQL, UNKNOWN and NULL are two distinctly different concepts. Only (so far as I'm aware) mysql conflates the two.

score 0 · Answer 3 · edited Mar 19 '17 at 21:16

0

In SQL NULL means it has some value but we don't know what is it. that's why when we compare we use like this

Select * from table [table name] where [column name] is not NULL

edited Mar 19 '17 at 21:16

marc_s

732,580
175
1,330
1,459

answered Jun 25 '15 at 03:10

Vivek

9
2

We cant write[ column name]<>NULL – Vivek Jun 25 '15 at 03:11

score 0 · Answer 4 · answered Jun 25 '15 at 06:20

The thing is that Sql Server engine is build upon three-valued predicate logic. If predicate compares 2 non null values then it can be evaluated to TRUE or FALSE. If at least one of then is NULL then predicate evaluates to third logical value - UNKNOWN.

Now what happens in WHERE clause? It is designed in such a way that it returns rows where predicate evaluates to TRUE only! If predicate evaluates to FALSE or UNKNOWN then corresponding row is just filtered out from resultset.

At first this is very confusing and leads newcomers into world of SQL to several typical mistakes. They just don't think that data may contain NULLs. One of classic mistake is for example:

Employyes(Name varchar, Contry varchar) 
'John', 'USA'
'Peter', NULL
'Mike', 'England'

And you want all rows where Contry is not USA. And you just write:

select * from Employees where Country <> 'USA'

and get only:

'Mike', 'England'

as a result. This is very confusing at first glance, but as far as you understand that engine is doing three-valued logic the result is logical.

Why SQL does finally treat a NULL value as FALSE?

4 Answers4