How to Check If Field Is Null or Empty in MySQL

How do I check if a column is empty or null in MySQL?

This will select all rows where some_col is NULL or '' (empty string)

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

How to check if field is null or empty in MySQL?

Either use

SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 
from tablename

or

SELECT case when field1 IS NULL or field1 = ''
            then 'empty'
            else field1
       end as field1 
from tablename

If you only want to check for null and not for empty strings then you can also use ifnull() or coalesce(field1, 'empty'). But that is not suitable for empty strings.

Check if a field is empty and get value from another column

You can use conditional IF to check for empty stings

SELECT ti.*,
       tt.text_en,
       IF(tt.text_es =  '', tt.text_en,tt.text_es) as text_es,
       IF(tt.text_pt = '', tt.text_en,tt.text_pt) as text_pt,
       IF(tt.text_fr = '', tt.text_en,tt.text_fr) as text_fr
FROM table1_items ti JOIN
     table_translations tt
     ON tt.name =  ti.name AND tt.cat = ti.cat;

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

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.

Check field if empty

Null and empty means NULL + '' (empty string)?

select CASE WHEN description IS NULL or description = '' THEN 'null or empty'
  ELSE 'not null' END 
  AS 'description'

In your original query, there is no possibility of a third case because IS NULL and IS NOT NULL are complementary, between them they have covered all possibilities.

Also, unless you are using case-sensitive collation (very rare, and never by default unless you specifically nominate one), MySQL is not Oracle - these two queries will work the same:

where name like '%Test%' or name like '%test%'
where name like '%test%'

Because MySQL will match strings case-insensitively

Checking if a column is not null or empty

I think this is clearer

COALESCE(v.file_name,'') != '' AND COALESCE(v.file_last_known_location,'') != ''

On some systems this may perform worse (as @sgeddes notes) against indexed columns.

Check whether a column is empty

Check whether it is null or empty string.

 Select * FROM Items WHERE Extra IS NULL OR Extra = ''

CakePHP 2.x Field is Empty or null

I think this might work for you:

$query['conditions']['OR'] = [
  'team.product_group' => "",   
  'team.product_group IS NULL',
];

With there being multiple OR clauses in your conditions array now, this might work:

$query['conditions']['AND'] = [
  'OR' => [
    'team.product_group' => "",   
    'team.product_group IS NULL',
  ],
];

From the docs Complex Find Conditions:

You can create very complex conditions by nesting multiple condition arrays:

array(
    'OR' => array(
        array('Company.name' => 'Future Holdings'),
        array('Company.city' => 'CA')
    ),
    'AND' => array(
        array(
            'OR' => array(
                array('Company.status' => 'active'),
                'NOT' => array(
                    array('Company.status' => array('inactive', 'suspended'))
                )
            )
        )
    )
)

---

Related Topics