Using IF in MySQL's SELECT subquery

Question

I have the following query:

  SELECT `users`.`id`, `login`, `fullname`, `active`, `sex`, `height`,
  `special-title`, `language`, `body-fat`,
  `main-photo-id`, `main-photo-offset`, `weight`,
  `objective`, `level`, `config-comments-type`,
  (
     SELECT `type`
     FROM `users-pro`
     WHERE
        (`user` = `users`.`id`)
        AND
        (`starts` <= $time)
        AND(
           (`ends` > $time)
           OR
           (`ends` IS NULL)
        )
     LIMIT 1
  ) as `account_type`
  FROM `users`

I'm wondering how/where do I add an IF statement, so that if the inner SELECT returns NULL (no entries for the user in users-pro, a value 1 would be returned.

This is what I usually do if there is no subquery:

SELECT IF(`rank` = 1, `rank`, 0) as `rank`

However, I don't know how to do this with a subquery.

Gordon Linoff · Accepted Answer · 2013-06-21 18:39:12Z

2

You can use the ANSI standard coalesce() function for this, by wrapping the subquery in it:

SELECT `users`.`id`, `login`, `fullname`, `active`, `sex`, `height`,
  `special-title`, `language`, `body-fat`,
  `main-photo-id`, `main-photo-offset`, `weight`,
  `objective`, `level`, `config-comments-type`,
  coalesce((
     SELECT `type`
     FROM `users-pro`
     WHERE
        (`user` = `users`.`id`)
        AND
        (`starts` <= $time)
        AND(
           (`ends` > $time)
           OR
           (`ends` IS NULL)
        )
     LIMIT 1
  ), 1) as `account_type`
  FROM `users`

answered Jun 21, 2013 at 18:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frantisek Over a year ago

That seems like a solution, yeah. I'm wondering whether there are no more options for this, but I think I should accept it.

georgepsarakis Over a year ago

Although COALESCE will work fine, I believe the most appropriate function from a logical standpoint is IFNULL since it returns another value in case an expression is NULL. Just to clarify: COALESCE accepts multiple arguments and returns the first non-NULL value.

Gordon Linoff Over a year ago

@setsuna . . . I have a strong preference for using ANSI standard constructs instead of database-specific ones, which is why I generally prefer coalesce().

Collectives™ on Stack Overflow

Using IF in MySQL's SELECT subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related