MySQL "unknown"

Question

I just discovered you can write something like

SELECT NULL IS UNKNOWN

Which returns 1.

Are there any other places you can use UNKNOWN? It doesn't appear to be a keyword (can't do SELECT UNKNOWN). NULL IS NULL is also true, so what purpose does UNKNOWN have?

Answer 1

UNKNOWN is just an alias for BOOLEAN NULL... the same way that TRUE is just an alias for 1 and FALSE is just an alias for 0, since in MySql BOOLEAN itself is just an alias for TINYINT(1)

Why is it even there? Because it's part of the SQL-92 standard:

<truth value> ::=
                TRUE
              | FALSE
              | UNKNOWN

Why can you SELECT NULL, SELECT TRUE, SELECT FALSE, but not SELECT UNKNOWN? Probably just a bug, since UNKNOWN itself wasn't supported until newer versions of MySql.

Answer 2

Seems to me, in MySQL, UNKNOWN is an alias for NULL, used in a Boolean sense.

I could find this reference:

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN).

MySQL docs - 12.3.3. Logical Operators

Some more information on general SQL NULL and UNKNOWN:

When restricted by a NOT NULL constraint, the SQL BOOLEAN works like the Boolean type from other languages. Unrestricted however, the BOOLEAN datatype, despite its name, can hold the truth values TRUE, FALSE, and UNKNOWN, all of which are defined as boolean literals according to the standard. The standard also asserts that NULL and UNKNOWN "may be used interchangeably to mean exactly the same thing".

NULL on Wikipedia