Mojtaba Mojtaba - 3 months ago 11
MySQL Question

MySQL - Which way is better to check if a column is null or empty

Well, when we are going to select records which include nothing in a column, we can use this:

SELECT * FROM my_table WHERE NULLIF(my_column, '') IS NULL;


But, in most cases, I see developers use this:

SELECT * FROM my_table WHERE my_column IS NULL OR my_column = ''


Is there any preference between these two? (e.g. performance, default support, version support, etc.)

If yes, please explain.

Answer

Calling a function on a field (in a condition) automatically discards any potential indexes on that field that could be used to speed up the query. Using OR in a list of conditions generally does the same (but for all indexes).

However, with the former, you end up making a function call for every row; with the latter there is the possibility to take advantage of short circuit evaluation.

Of course, there is a third option which can make use of indexes...

SELECT * FROM my_table WHERE my_column IS NULL
UNION
SELECT * FROM my_table WHERE my_column = ''

but then the cost of the UNION/two queries may outweigh the benefit of index use; depending on actual data.

Comments