MySQL – CASE vs IF Statement vs IF function

Who can please explain the difference between CASE-statement, IF-statement and IF-function?

What is the difference in terms of usage and “how it works”?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

From the manual, it looks like the if function is just a less flexible form of the case expression. For example, you could write:

select if(username = 'darxysaq', 'high', 'low') as awesomeness

And the equivalent with case:

select case when username = 'darxysaq' then 'high' else 'low' end as awesomeness

But case is more flexible. It allows more than one branch, like:

select case 
       when username = 'darxysaq' then 'high' 
       when username = 'john skeet' then 'medium' 
       else 'low' 
       end as awesomeness

And it can act like a switch:

select case username 
       when 'darxysaq' then 'high' 
       when 'john skeet' then 'medium' 
       else 'low' 
       end as awesomeness

Now the if statement is an entirely different beast. It is a control statement in MySQL procedures. The statement form looks like:

CREATE FUNCTION GetAwesomeness (username varchar(50))
RETURNS varchar(20)
BEGIN
   IF username = 'darxysaq' THEN
      return 'high';
   ELSEIF username = 'john skeet' THEN
      return 'medium';
   ELSE
     return 'low';
   END IF;
END; //

Here’s a SQL Fiddle with the statement version. It looks like Mr Bean isn’t all that he’s made up to be!

A final note: the case expression is standard SQL and works in most databases. The if function is not standard SQL and will not work in other databases, like SQL Server or PostgreSQL.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0