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
Joining Tables from Different Servers
How to Delete Multiple Rows in Diferent Tables
Oracle Pl/Sql String Compare Issue
Get Last Record of a Table in Postgres
Sql - Finding Students Taking 2 or More Classes
Use Multiple Conflict_Target in on Conflict Clause
Check If a Time Is Between Two Times (Time Datatype)
Mysql Error 1449: the User Specified as a Definer Does Not Exist
Comparing Two Columns Using SQL to Make Sure the Same Set of Values Are Present in Both
Sql Server - Join Rows into Comma Separated List
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
Calculating Percent Change Between Two Rows
What Is and How to Remove Tablespace Error from My Database
State Wise Data of Country and City
Mysql Error 1264: Out of Range Value for Column
Regex Pattern Inside SQL Replace Function