CODEWITHSUNDEEP
mysqlmariadb
Cornwell
Cornwell

Reputation: 3410

Check if subquery is NULL

I have this query:

SELECT sid FROM (SELECT * FROM myTable WHERE keyword='tank') AS InnerTmp LIMIT 1

Sometimes the inner query is NULL. I want to replace NULL with 0. I tried this, but it doesn't work:

SELECT sid FROM (SELECT IFNULL(sid,0) AS sid FROM myTable WHERE keyword='tank') AS InnerTmp LIMIT 1

Upvotes: 3

Views: 14396

Answers (2)

citizen404
citizen404

Reputation: 1515

The problem is that you're only testing if sid is NULL and if so you SELECT 0, but if not you're selecting 0, too. That's because if the IFNULL condition is not satisfied it returns 0 (FALSE).

You have to use an IF clause and check if ISNULL(sid) is 1 (TRUE). If that's the case then SELECT 0 else SELECT sid.

SELECT sid FROM (
    SELECT IF(ISNULL(sid), 0, sid) FROM myTable WHERE keyword='tank') AS InnerTmp
    LIMIT 1

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562701

Do you mean when there are no rows where keyword='tank' then the subquery will return zero rows?

Here's a way to work around that: use an outer join so you get at least one row, and match that to the set of rows where keyword='tank'. If there are no such rows, it still returns the one row from the other side of the outer join. Then you can use COALESCE() to default the sid to the dummy zero value. 

SELECT sid FROM (
  SELECT COALESCE(myTable.sid, t.placeholder) AS sid
  FROM (SELECT 0 AS placeholder) AS t 
  LEFT OUTER JOIN myTable ON keyword='tank'
) AS InnerTmp LIMIT 1

Upvotes: 5

Related Questions