I'm confused what does UNKNOWN means in SQL in 3 valued logic. Does it mean NULL actually? Is it true that NULL and UNKNOWN can be interchangeable in all boolean contexts?
Asked
Active
Viewed 7,883 times
7
-
No, unknown is unknown. It's not null. Try here for a start https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/ – Rich Benner Dec 16 '16 at 09:01
-
6The official SQL Standard "does not make a distinction between the null value of the boolean data type and the truth value Unknown" (§4.5 ISO/IEC 9075-2:2016) it goes on to say "they may be used interchangeably to mean exactly the same thing". – sirain Mar 10 '20 at 12:06
3 Answers
8
Simple answer:
3 == 2 => FALSE
3 == 3 => TRUE
NULL == 3 => UNKNOWN
3 == NULL => UNKNOWN
NULL == NULL => UNKNOWN
Expression with NULL on either side or both evaluates to UNKNOWN.
For example if you have a table:
Employees(id, country)
1, USA
2, USA
3, Canada
4, NULL
Say your boss asks you to select all employees that don't live in USA. You write:
select * from Employees
where country <> 'USA'
and get:
3, Canada
Why 4 is not selected? Because in WHERE clause only rows are returned where expression evaluates to TRUE:
1. USA <> 'USA' => FALSE -- don't return this row
2. USA <> 'USA' => FALSE -- don't return this row
3. CANADA <> 'USA' => TRUE -- return this row
4. NULL <> 'USA' => UNKNOWN -- don't return this row
It is not only for equality or inequality. For any predicate
Giorgi Nakeuri
- 35,155
- 8
- 47
- 75
-
1but in my db comparison with null also returns null where it should be unknown, so I am confused where unknown actually is the same thing as null – Michael Tsang Dec 16 '16 at 10:00
-
-
2@MichaelTsang - in [tag:mysql], `NULL` and `UNKNOWN` are treated the same. As I indicate in my answer, this is by no means universal across SQL database systems. – Damien_The_Unbeliever Dec 16 '16 at 10:49
4
According to Wikipedia:
NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing"
However, some database systems don't in fact implement SQL's boolean data type (it's optional) and in most of those systems, there is no circumstance where you'd encounter both UNKNOWNs and NULLs within the same context - UNKNOWNs only appear when evaluating predicates.
The are various tools you can use to try to eliminate NULLs, such as COALESCE or IS [NOT] NULL. Most of these cannot be used in the context of evaluating predicates and so will never be used with an UNKNOWN value. E.g. if you have a query like:
SELECT
*
FROM
TableA
WHERE A = 'B'
And you know that there are some NULL A values which are thus causing the WHERE clause predicate to produce UNKNOWN, you cannot write:
SELECT
*
FROM
TableA
WHERE COALESCE(A = 'B',TRUE)
To eliminate the UNKNOWN.
Damien_The_Unbeliever
- 234,701
- 27
- 340
- 448
-
1Your last example about coalesce is actually workable in MySQL which supports predicate expressions as values. – Michael Tsang Jun 17 '19 at 14:23
