MySQL – CASE vs IF Statement vs IF function

Question

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.

Leave a Comment Cancel reply