MySQL - CASE vs IF Statement vs IF function

Question

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"?

Answer 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.