CODEWITHSUNDEEP
mysqlif-statementselect
Marco Aurélio Deleu
Marco Aurélio Deleu

Reputation: 4367

If Statement in MySQL Select

According to the docs a if works as follows:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3 [...].

Here is a random fiddle that represents my problem: http://sqlfiddle.com/#!9/8076e2/1

Basically what I'm trying to do is the following:

SELECT IF(whatever, 1, 2) FROM testing WHERE whatever = 'abc';

Since there is a record there that matches my WHERE clause it basically means that whatever won't be 0 or NULL like specified in the docs. Then why am I getting expression3 as result?

Upvotes: 1

Views: 70

Answers (2)

Ike Walker
Ike Walker

Reputation: 65587

expr1 is intended to be a boolean expression, or at least a numeric value, not a string.

You can use true and false, or 1 and 0, etc. Technically any non-zero numeric value is interpreted as true.

Since you are using the string 'abc' as expr1, MySQL is implicitly converting that to the number 0, which represents false.

In order to return one result for a non-empty string, and another result for empty string or null, you can use a query like this:

SELECT if((whatever is not null and whatever != ''), 1, 2) 
FROM testing 
WHERE whatever = 'abc';

You can also do the same thing with CASE if you want to follow the SQL standard:

SELECT (case when (whatever is not null and whatever != '') then 1 else 2 end) 
FROM testing 
WHERE whatever = 'abc';

Upvotes: 2

dokgu
dokgu

Reputation: 6080

SELECT IF(COUNT(whatever) > 0, 1, 2) FROM testing WHERE whatever = 'abc';

Upvotes: 1

Related Questions