Who can please explain the difference between
What is the difference in terms of usage and “how it works”?
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.
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
select case when username = 'darxysaq' then 'high' else 'low' end as awesomeness
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
select case username when 'darxysaq' then 'high' when 'john skeet' then 'medium' else 'low' end as awesomeness
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.