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

Question

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 1

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.

Answer 2

As per my knowledge best way with respect to execution time or performance is :

SELECT * FROM my_table WHERE my_column = ''

Reference : How do I check if a column is empty or null in mysql

Answer 3

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.

Answer 4

The two constructs are not the same. From the MySQL documentation page:

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. 
This is the same as:
  CASE WHEN expr1 = expr2 
  THEN NULL 
  ELSE expr1 
  END.

Therefore, this logic is testing a column for "NULL or an empty string."

• If my_column is presently NULL, the function will return its value (NULL ...) because it is not equal to an empty-string.
• If my_column is an empty string, the function will return NULL.
• If any other value, that value will be returned.

I heartily agree with Naveed's answer in saying, "this is 'cute' ... don't be 'cute'." His answer ("use OR ...") results in very simple SQL that is instantly obvious at first glance. This "clever" solution is not. (And it is probably slightly-less efficient, to boot.)

And, of course, you'd really like to avoid the necessity for this sort of thing when designing your database. You never want to store "empty strings" in a VARCHAR-type field unless it truly is the case that "we have a non-NULL value, and that value really is 'an empty string.'"

Answer 5

Generally, the reason why people will include the ='' portion is because SQL Server stores that as an empty string, which is not the same as NULL.

Link to reference: openshac's answer