MySql, the world's most popular open source database has many features. One of them is the IF statement and the other is CASE statement. Let's get to know them better here.
MySQL IF() takes three expressions and if the first expression is true, not zero and not NULL, it returns the second expression. Otherwise, it returns the third expression. Depending on the context in which it is used, it returns either numeric or string value.
IF(expression, expr_true, expr_false);
Consider the following table, users, with name, country and status. The status has 0 or 1 value which represents inactive and active states respectively.
CREATE TABLE users (name varchar(64), country varchar(64), status tinyint(1)); INSERT INTO users (name, country, status) VALUES ('JD', 'India', 1), ('Bob', 'China', 0), ('Alic', 'Japan', 1);
When you fire a select query, all you get in the status field is the integer value. Now if you want to change the output to be active or inactive based on the actual value of the status column then you can go with IF statement.
SELECT name, country, IF(status = 1, "Active", "Inactive") FROM users;
Let's add one more row to the table with NULL status value.
INSERT INTO users (name, country, status) VALUES ('Pallavi', 'India', NULL);
Now, our condition fails to check the NULL value. In order to handle the null value we use IS NULL operator from MySql in the IF statement. We'll now have two nested IF statements. Check out the below query to get the proper understanding.
SELECT name, country, IF(status IS NULL, 'Undefined', IF(status = 1, "Active", "Inactive")) `status` FROM users;
Now you got to know the basic working of the IF statement. As we nested our conditions, in the same fashion we can add multiple conditions which evaluate to either true or false.
Coming to the next part of this article, we have other functionality the CASE statement. CASE statement can be said as the switch statement for MySql.
Let's take a look at the syntax
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END
We'll now alter our SELECT query to work with the case statement. Now we have three different values for the status column, thus CASE is an appropriate choice for the SELECT query.
SELECT name, country, (CASE status WHEN 1 THEN "Inactive" WHEN 0 THEN "Inactive" ELSE "Undefined" END) AS status FROM users;
As you can see, the above query is pretty readable than the nested IF statement query and by using the WHEN clause we can add multiple values here. This can be used in many different scenarios, like joins. When you have JOINs on two tables and you want to select the value based on certain conditions the CASE statements comes handy.
In case you have any doubt or need any advanced explanation, do let me know in the comments. I hope you understand both the statements well. Signing off for the day!