Where NULL IS NULL

Question

I'm seeing a lot of where NULL IS NULL type conditions in code and I'm curious about the overhead that may be involved in this.

So given SELECT * FROM table WHERE field = 'TERM' AND (NULL IS NULL OR anotherField = NULL) on a table > 10+ million records. Would there be enough concern to enforce the removal of the NULL IS NULL condition or the difference is negligible?

Developers are using this syntax to write generic SQL that handles optional query parameters. Personally I feel it should be left off the query if the parameter is not provided as it seems redundant.

I've done a little bit of Googling on this and I can't seem to find the answer, maybe my search terms are off as it seems common.

score 2 · Accepted Answer · answered May 15 '18 at 05:00

I've seen this before and I did a quick search to verify it. I'll tell you what I think you're looking just as a guess, from use-the-index-luke

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
  WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
    AND ( employee_id      = :emp_id OR :emp_id IS NULL )
    AND ( UPPER(last_name) = :name   OR :name   IS NULL )
The query uses named bind variables for better readability. All possible filter expressions are statically coded in the statement. Whenever a filter isn't needed, you just use NULL instead of a search term: it disables the condition via the OR logic.

So I think you're looking at is dynamically generated SQL. You have

SELECT * FROM table WHERE something = 'TERM' AND NULL IS NULL

I don't think that's right, I think that's a placeholder or prepared statement that looks like this,

SELECT * FROM table WHERE ($1 = 'TERM' OR $1 IS NULL);

And ultimately that's whole statement gets left empty so it looks like,

SELECT * FROM table WHERE (null = 'TERM' OR null IS NULL);

That makes it so the conditional loses all selectivity and is pruned in query compilation. That's all background,

Would there be enough concern to enforce the removal of the NULL IS NULL condition or the difference is negligible?

Yes, the null IS NULL gets constant folded with true (in every database I know about), and both false OR true and true OR false gets simplified to true.

Where NULL IS NULL

1 Answers1