DEV Community

yuyabu
yuyabu

Posted on

SQL:How to compare with NULL without "IS NULL" operator

#sql #null #rdbms #database

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)
Enter fullscreen mode Exit fullscreen mode 
$ sqlite3 --version 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

other

IS DISTINCT FROM operator in MySQL is <=>

reference

Top comments (1)

Collapse
 
pawsql profile image
Tomas •

Check out all you need to know about SQL Processing with Null Values in this post dev.to/pawsql/four-pitfalls-of-sql...