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

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. — MatBailie, Mar 22 '12 at 12:30
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`. — Martin Smith, Mar 22 '12 at 14:01
What are we supposed to be looking at on page 335? Sure you got the right page number? That is about the `` — Martin Smith, Mar 22 '12 at 14:07
@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. — Janus Troelsen, Mar 22 '12 at 14:50
I think `IS TRUE`, `IS FALSE`, `IS UNKNOWN` are special predicates for dealing with the boolean datatype. [This product seems to use them](http://docs.actian.com/ingres/10.0/release-summary/2140-boolean-data-type) — Martin Smith, Mar 22 '12 at 15:37
@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. — Janus Troelsen, Apr 14 '21 at 15:02

Martin Smith · Accepted Answer · 2012-03-22T16:31:27.097

29

That's a new one for me.

If I read that correctly 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.

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 |
+---------+---------+---------+---------+

edited Mar 22 '12 at 16:31

answered Mar 22 '12 at 15:57

Martin Smith

438,706
87
741
845

1

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) – Jonathan Leffler Mar 22 '12 at 17:07
1

(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. – Jonathan Leffler Mar 22 '12 at 17:12
@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 ```` on printed page number 281. – Janus Troelsen Mar 23 '12 at 00:57
1

@JonathanLeffler Also see section 8.7 ````, 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 ```` (special) semantics, but by the ```` semantics, which would seem to make ``IS NOT UNKNOWN`` equal to ``IS UNKNOWN`` (as page 281 notes in "General Rules" bullet 2). – Janus Troelsen Mar 23 '12 at 01:09
@JonathanLeffler None of the truth tables in the standard are asymmetric, and therefore do not support your claim about significant argument order (except in ```` cases where the order obviously matters because it's not the operator anymore). – Janus Troelsen Mar 23 '12 at 01:10
The references in these comments are for the linked SQL:2003 draft. – Janus Troelsen Mar 23 '12 at 01:12

score 6 · Answer 2 · edited Sep 23 '16 at 13:00

6

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.

edited Sep 23 '16 at 13:00

AlG

14,697
4
41
54

answered Mar 22 '12 at 15:10

Chris

377
2
11

2

***It will return NULL(false)*** is not a valid statement. `NULL !== FALSE`, `NULL !== TRUE`, `NULL !== NULL`. It is just *undefined*. – Fr0zenFyr Oct 07 '17 at 12:27

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

2 Answers2

Linked