MySQL - CASE vs IF function and IF function

Question

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

What is the difference in terms of use and how it works?

Answer 1

From the manual , it seems that the if function is a less flexible form of the case expression. For example, you can 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 more flexible. It allows you to use several branches, for example:

 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 is a completely different beast. This is the statement. Here's the SQL Fiddle with the statement version. Mr Bean doesn't seem to be all he did!

Last note: the case is standard SQL and works on most databases. The if function if not standard SQL and will not work in other databases such as SQL Server or PostgreSQL.