Is SQL unknown identical to NULL?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

Read this link hope its helpfull for you

https://msdn.microsoft.com/en-us/library/mt204037.aspx