Standard SQL boolean operator IS vs. equals (=) operator

Question

On the Wikipedia page for SQL there are some truth tables about boolean logic in SQL. [1] The Wikipedia page seems to source the SQL:2003 standard.

The truth table for the equals operator (=) is different from the IS operator from the SQL:2003 draft.

Also, the Wikipedia article notes that "IS NULL" (<null predicate>) is a special case.

In the SQL:2003 it seems that there is an "IS" opeartor which is a regular operator like AND, NOT and OR. However, the <null predicate> is still there.

Why is the <null predicate> there when the IS is a regular boolean operator? Is it to make sure you can use the "IS NULL" construct with non-boolean values without type coersion? Is it discouraged to use "=NULL"?

Does the SQL:2011 standard work differently?

[1]: Wikipedia on SQL

[2]: SQL:2011 draft PDF page 335

[3]: SQL:2003 draft PDF page 397

Misjoinder

Nothing equals NULL, not even NULL. So NULL = NULL, NULL = 1 and 1 = NULL are all false (Well, really they're all NULL / Unknown which is not true and so is really close to being false). But NULL IS NULL is true. – Phono

I haven't read your links yet. SQLite has an IS operator that is interchangeable with = except for it has the semantics that NULL=NULL. – Radloff

What are we supposed to be looking at on page 335? Sure you got the right page number? That is about the <from clause> – Radloff

@MartinSmith: The page numbers on the 2011 version differ between PDF and paper. So when I say "PDF page" I mean the page you get when you "go to" page 335 with your PDF reader. I get the same page with Evince and Adobe, so I think it's a reliable method. – Misjoinder

@MartinSmith Printed page number: 312 – Misjoinder

I think IS TRUE, IS FALSE, IS UNKNOWN are special predicates for dealing with the boolean datatype. This product seems to use them – Radloff

@Dems - This seems to be a different use of IS though... – Radloff

@ShamsulArefin Can you please update them for me? Then I will review whether your updates are acceptable. If they are not acceptable, I will ask you to work harder. By working for me, you can improve your value as a human being. If you do not work for me, you will never improve. – Misjoinder

Answer 1

The Modern SQL page on three valued logic mentions these and says F571 was added as an optional feature in SQL:1999.

The <boolean value expression> grammar defines three predicates solely for use with the boolean datatype IS TRUE, IS FALSE, IS UNKNOWN.

These differ from their equality counterparts in that they only evaluate to True or False. Never to Unknown. i.e. UNKNOWN = TRUE would evaluate to UNKNOWN but UNKNOWN IS TRUE evaluates to False.

A case where they are useful would be when wanting to return rows where <some_expression> is either true or unknown.

In products that support this syntax this can be done without needing to repeat <some_expression> by just writing

WHERE <some_expression> IS NOT FALSE

The full truth tables for IS and = are below.

+---------+-------+-------+---------+
|   IS    | TRUE  | FALSE | UNKNOWN |
+---------+-------+-------+---------+
| TRUE    | TRUE  | FALSE | FALSE   |
| FALSE   | FALSE | TRUE  | FALSE   |
| UNKNOWN | FALSE | FALSE | TRUE    |
+---------+-------+-------+---------+

As opposed to

+---------+---------+---------+---------+
|    =    |  TRUE   |  FALSE  | UNKNOWN |
+---------+---------+---------+---------+
| TRUE    | TRUE    | FALSE   | UNKNOWN |
| FALSE   | FALSE   | TRUE    | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+

Radloff

Note that the operators are 'IS TRUE', 'IS FALSE', 'IS NULL' and 'IS UNKNOWN'. They are unary operators, evaluating the LHS expression against the RHS condition. The IS table has to be read as 'operator across the top, operand down the side'; UNKNOWN IS UNKNOWN evaluates to TRUE. Also beware that the LHS can be a 'row type' (probably not the correct technical term), and then the evaluation goes over each of the items in the LHS. Also, the operators can be negated 'IS NOT NULL', 'IS NOT TRUE', 'IS NOT FALSE', 'IS NOT UNKNOWN'. That can lead to major headaches. (Cont'd) – Dekker

(Cont'd): IIRC, with a row, 'row IS NOT NULL' and 'NOT (row IS NULL)' are not the same answer, necessarily. I think the difference is that 'NOT (row IS NULL)' evaluates to true if some column is not null, but 'row IS NOT NULL' evaluates to true only if all columns are not null. For a single value in the 'row', these are the same; for multiple values, they are not necessarily the same. – Dekker

@JonathanLeffler The standards (2003 and 2011) use the term "the IS boolean operator". If they were independent unary operators, wouldn't they use plural? See section 6.34 <boolean value expression> on printed page number 281. – Misjoinder

@JonathanLeffler Also see section 8.7 <null predicate>, table 14 (printed page 398) where it seems that R IS NULL, NOT R IS NULL, R IS NOT NULL, NOT R IS NOT NULL are the only special cases, which means that IS NOT TRUE and the like would be interpreted not by <null predicate> (special) semantics, but by the <boolean value expression> semantics, which would seem to make IS NOT UNKNOWN equal to IS UNKNOWN (as page 281 notes in "General Rules" bullet 2). – Misjoinder

@JonathanLeffler None of the truth tables in the standard are asymmetric, and therefore do not support your claim about significant argument order (except in <null predicate> cases where the order obviously matters because it's not the operator anymore). – Misjoinder

The references in these comments are for the linked SQL:2003 draft. – Misjoinder

Answer 2

As the above poster said, null = null is not correct. It will return NULL(false)

For null comparison you must use IS NULL or IS NOT NULL.

Overland

It will return NULL(false) is not a valid statement. NULL !== FALSE, NULL !== TRUE, NULL !== NULL. It is just undefined. – Complexion