CODEWITHSUNDEEP
mysql
TrickOrTreat
TrickOrTreat

Reputation: 911

Return something else other than NULL or EMPTY via mysql query

I am trying to determine whether id is present or not in my MySQL table. If not I need to return -1 instead. So I tried IFNULL. However I am still getting empty.

select ifnull(id, -1) as id from docs  where id=3909090 LIMIT 1

I am supposed to get -1 here. Any suggestions?

Upvotes: 0

Views: 82

Answers (3)

Nick
Nick

Reputation: 147146

You can COUNT the number of rows with id = 3909090 and if 0, return -1, otherwise the id value:

select case when count(*) = 0 then -1
            else min(id)
       end as id
from docs  
where id = 3909090

Note we use min(id) to avoid issues with grouping.

Demo on dbfiddle

Upvotes: 1

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can use IFNULL() this way

SELECT IFNULL( (select id from docs where id=3909090 LIMIT 1) ,-1) AS ID;

Demo on DB<>Fiddle

Upvotes: 3

CaffeinatedCod3r
CaffeinatedCod3r

Reputation: 882

You can use NOT EXISTS for this. 

select -1 as id from docs where NOT EXISTS (SELECT * FROM docs WHERE id=3909090 )

NOT EXISTS will return True if a record is not found in the table with that id. Since WHERE condition is true it will print outer query value which is -1.

Upvotes: 1

Related Questions