DEV Community

yuyabu
yuyabu

Posted on

3 1

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

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@PawSQL

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

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay