MySQL "unknown"

Question

I just discovered you can write something like

SELECT NULL IS UNKNOWN 

Returns 1 .

Are there other places where you can use UNKNOWN ? It is not a keyword (cannot do SELECT UNKNOWN ). NULL IS NULL also true, so what purpose does UNKNOWN ?

Answer 1

UNKNOWN is just an alias for BOOLEAN NULL ... in 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? Since it is 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 mistake, since UNKNOWN itself was not supported until new versions of MySql .

Answer 2

It seems to me that in MySQL UNKNOWN is an alias for NULL , used in a logical sense.

I could find this link:

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

MySQL docs - 12.3.3. Logical operators

Additional information on common SQL NULL and UNKNOWN:

When limited by NOT NULL, SQL BOOLEAN works like a Boolean type from other languages. Unlimited, however, the BOOLEAN data type, despite its name, can contain TRUE, FALSE, and UNKNOWN truth values, all of which are defined as logical literals according to the standard. The standard also states that NULL and UNKNOWN "can be used interchangeably to mean the same thing."

NULL on Wikipedia

Answer 3

Used only to describe a logical value that does not have a known value. Just like in the first example, this is the logical equivalent of NULL .