Nullable Parameters in SQL Queries
A common beginners' trap in SQL is the NULL value. Nothing equals NULL, not even itself. Imagine a table called foo with a nullable column called bar.
-- THIS IS A BROKEN QUERY!
SELECT * FROM foo WHERE bar = NULL;
That query shouldn't return any results because comparing any value to NULL will evaluate as false. To find rows in foo where bar is NULL will require, well, IS NULL.
SELECT * FROM foo WHERE bar IS NULL;
That's all well and good when you're writing custom queries by hand. Just remember to use IS NULL. But what about when you're writing a parameterized query and one of the parameters may be NULL, or it may be a value?
I've seen some tricks out there that use COALESCE. But if you're using PostgreSQL, you're in luck. There's a very nice, clean solution available: IS NOT DISTINCT FROM.
PREPARE my_query (integer) AS
SELECT * FROM foo WHERE bar IS NOT DISTINCT FROM $1;
EXECUTE my_query(1);
EXECUTE my_query(NULL);
If you've never heard of the IS NOT DISTINCT FROM operator, neither had I until today. I'm glad it exists, but using the word "distinct" makes this handy operator very difficult to search for online.
For the sake of readability, I'd recommend only using this operator in this particular situation. In other words, don't rush to go change every usage of = and IS NULL in queries that don't need this special consideration. This is a special tool that deserves a spot in your tool belt for just the right occasion.