SQLite Forum

When is "IS" used in SQLite?
Login
Sorry for nitpicking, but I can't resist noticing that `null` next to “semantics” forms an oxymoron :)

To expand on the above: `is` differs from `==` only when the considered expression contains a `null`. The difference is that `==` obeys *Codd's Substitution Principle*, while `is` does not.

According to Codd's Substitution Principle, an expression has the truth value “unknown”, if and only if:

1. each occurrence of null may be replaced by a (distinct or already used) non-null value so that the expression’s truth value becomes “true”, and

2. each occurrence of null may be replaced by a (distinct or already used) non-null value so that the expression’s truth value becomes “false”.

This is a pretty ad-hoc principle, which (1) was formulated by Codd strictly under the “existing, but unknown value” interpretation of nulls (but it is logically incompatible with such an intepretation), and (2) leads to many unresolvable inconsistencies in the treatment of nulls in SQL. Unfortunately, that is cast in stone in the standard, so one has to live with it. The best way to live with it is to avoid using nulls altogether.

Re (1), I'll just note that `null` cannot be interpreted as “value at present unknown” in general, because, if that were the case, then `select x=x from T` should always return true, even when `x` is `null`.

`IS` has instead a clean semantics. Any expression that contains a `null` `IS` (the same) as any other expression that contains `null` and `IS NOT` (the same a s) any other expression not containing a `null` (so, it's either true or false). This rule is used, among the rest, for the elimination of duplicates:

```
create table T (x int);
insert into T values (1), (1), (null), (null);
select distinct x from T;
```

and for set operations:

```
select x from T intersect select x from T;
select x from T union select x from T;
select x from T except select x from T;
```

again, in accordance to what Codd himself warned being a "preliminary and in need of further research" proposal.