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.