Search code examples
mysqlisnull

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.

Solution

  • Evaluating the two expressions on a single row should not take such a big difference. The reason people use the second version is the use of indexes. Mysql has a special optimization for this and can use it even with the or.

    See IS NULL Optimization

    IS NULL Optimization

    MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

    If a WHERE clause includes a col_name IS NULL condition for a column that is declared as NOT NULL, that expression is optimized away. This optimization does not occur in cases when the column might produce NULL anyway; for example, if it comes from a table on the right side of a LEFT JOIN.

    MySQL can also optimize the combination col_name = expr OR col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used.