CODEWITHSUNDEEP
mysqlsql
U.B.A.R
U.B.A.R

Reputation: 231

mySQL query for empty and NULL value together

In a database, among the fields, I have two fields which are picName and UserName. The picName can have a NULL value, a picture's name or it can be empty.

I want to query the userName of those entries whose picName is either NULL or empty.

I tried these commands separately

Select userName from user where picName = ''

Select userName from user where picName IS NULL

But I want to get both the results in 1 query.

I tried using IN operator like this

Select userName from user where picName IN ('' , IS NULL)

But it didn't work.

How should I do it...?

Upvotes: 6

Views: 33791

Answers (7)

Fatalist
Fatalist

Reputation: 359

Solution that checks for both empty and NULL values.

COALESCE(NULLIF(table.column, ''), table.fallback)

So your query will be:

SELECT userName FROM user WHERE NULLIF(picName, '') IS NULL

Upvotes: 0

Kalle
Kalle

Reputation: 1

...ISNULL(picName ,'') is a function of MSSQL

select case when (picname is null or picname = '' then username else 'filled' end from user

gives you the UserName if picname = '' or null else the text 'Filled'. If you want to filter these ones use (like the other said): ...where picname is null or picname = ''

Upvotes: 0

Guru
Guru

Reputation: 1

select userName from user WHERE ISNULL(picName ,'') = ''

Upvotes: -1

eggyal
eggyal

Reputation: 125855

An alternative to the other answers is to use MySQL's IFNULL() or COALESCE() functions:

SELECT userName FROM user WHERE IFNULL(picName='',TRUE);
SELECT userName FROM user WHERE COALESCE(picName,'') = '';

Upvotes: 5

fvu
fvu

Reputation: 32953

You want one of both conditions to be satisfied, so condition one OR condition two must be true, and that's exactly what you need to write:

Select userName from user where picName = '' OR picName IS NULL

Upvotes: 1

Kermit
Kermit

Reputation: 34055

SELECT userName FROM `user` WHERE picName IS NULL OR picName = ''

Upvotes: 0

John Woo
John Woo

Reputation: 263703

use OR if you have multiple filters in your condition

Select userName 
from user 
where picName IS NULL OR 
      picName = ''

Upvotes: 19

Related Questions