In MySQL, can you check if a field is NULL or empty using only one comparison?

Question

If I want to check to see if a field is NULL or empty using a MySQL query, I know I can do something like this:

column = '' OR column IS NULL

However, is there any way to check this without doing two separate comparisons?

Answer 1

Use COALESCE() to 'normalize' the value (convert NULL values to an empty string);

WHERE COALESCE(mycolumn, '') = ''

Read the documentation: COALESCE()

Or the other way around; convert empty strings to NULL;

WHERE NULLIF(mycolumn, '') IS NULL

Documentation: NULLIF()

Of those two, I would prefer COALESCE() as it is part of the ANSI SQL standard

You can experiment with it yourself, just do this;

SELECT 
    mycolumn                      AS orig_value,
    COALESCE(mycolumn, '')        AS coalesce_value,
    (COALESCE(mycolumn, '') = '') AS compare_result

FROM mytable;

This will show the original value, the 'coalesce' value and the result of the comparison side by side for every row in the table

Answer 2

WHERE COALESCE(column, '') = ''