We use IS NULL instead of =for comparison with NULL. This is because there are many RDBMSs that become UNKNOWN if you compare null and value with =.
- comparison of some value and null to false
- comparison of null and null to true
Some times we want to compare it like that. In such a case we can use the comparison operator IS DISTINCT FROM orIS NOT DISTINCT FROM.
| A | B | A = B | A IS NOT DISTINCT FROM B |
|---|---|---|---|
| 0 | 0 | true | true |
| 0 | 1 | false | false |
| 0 | null | unknown | false |
| null | null | unknown | true |
environment and version
postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-apple-darwin13.4.0, compiled by Apple LLVM version 6.0 (clang-600.0.57) (based on LLVM 3.5svn), 64-bit
(1 row)
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d
Example on PostgreSQL
--compare null and value with '='( result is unknown)
postgres=# select null = 4;
?column?
----------
(1 row)
--compare value and value (result is true/false)
postgres=# select 4 = 4;
?column?
----------
t
(1 row)
--compare null and value with 'IS DISTINCT FROM' operator (result is true/false)
postgres=# select null is distinct from 4;
?column?
----------
t
(1 row)
-- use 'IS NOT DISTINCT FROM' to check equal
postgres=# select null is not distinct from 4;
?column?
----------
f
(1 row)
-- You can also compare values and values using 'IS DISTINCT FROM'
postgres=# select 4 is distinct from 4;
?column?
----------
f
(1 row)
sqlite: Use IS instead of IS DISTINCT FROM
sqlite can't use IS DISTINCT FROM.
You can compare with IS instead
sqlite> select 4 = 4;
1
sqlite> select 4 is null;
0
sqlite> select 4 is 4;
1
sqlite> select 4 is 5;
0
sqlite> select null is null;
1
other
IS DISTINCT FROM operator in MySQL is <=>
reference
- https://modern-sql.com/feature/is-distinct-from
- Joe Celko's SQL for Smarties: Advanced SQL Programming(Japanese version) 16. 3 IS [NOT] DISTINCT FROM operator
Oldest comments (1)
Check out all you need to know about SQL Processing with Null Values in this post dev.to/pawsql/four-pitfalls-of-sql...