MySQL 'if exists' error

Question

I got a error in my MySQL query:

if not exists(select * from tb_user where user_id=1) then
     select 'ok' as rul;
else
     select 'not' as rul;
end if;

Where's my problem?

You may also want to check on this performance of count(*) vs exisits post to embark in the best solution for you. :) — bonCodigo
– bonCodigo, Commented Dec 21, 2012 at 7:49

tom · Accepted Answer · 2012-12-21 07:28:03Z

4

The IF statement can only be used in stored functions. You can do what you want with the IF() function, as follows:

SELECT IF(EXISTS(select * from tb_user where user_id=1), 'ok', 'not') as rul;

answered Dec 21, 2012 at 7:28

tom

19.2k4 gold badges39 silver badges36 bronze badges

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

Comments

bonCodigo · Accepted Answer · 2012-12-21 07:41:05Z

3

Another method: You may also use case when

Select case
when exists(select * from tb_user where user_id=1)
then 'Ok'
else 'Not'
end
;

Sample table:

ID  NAME
1   john
2   tim
3   jack
4   rose

Query: renamed the columns as Status

Select case
when exists(select * from table1 where id=1)
then 'Ok'
else 'Not'
end as Status
;

Results:

STATUS
Ok

answered Dec 21, 2012 at 7:34

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Devart · Accepted Answer · 2012-12-21 07:33:54Z

1

SELECT IF(COUNT(*) > 0, 'ok', 'not') rul FROM tb_user WHERE user_id = 1;

answered Dec 21, 2012 at 7:33

Devart

120k23 gold badges171 silver badges189 bronze badges