CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110093

Checking if a column is not null or empty

Is there a better way to do the following to check if a column is not empty/null?

(v.file_name is not null and v.file_name != '' 
 and v.file_last_known_location is not null and v.file_last_known_location != '')

Upvotes: 0

Views: 184

Answers (2)

Hogan
Hogan

Reputation: 70513

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.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

I would say even though the current query looks clumsy, it will out perform the suggested answer when you have index on file_name and file_last_known_location.

The usage of function in Where clause will restrict optimizer from using Index. So better to use original query

(v.file_name is not null and v.file_name != '' 
 and v.file_last_known_location is not null and v.file_last_known_location != '')

Upvotes: 1

Related Questions