Condition In MySql Query

MySql IF and CASE statement

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.

Syntax:


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.

name country status
JD India 1
Bob China 0
Alic Japan 1

Table: users


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;

MySql If Statement

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;

 

MySql If Statement on NULL Value

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.

 

CASE statement

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.

The major difference between IF and CASE statement is that IF statement has a single condition and is a non-standard SQL function, whereas in CASE statement we can have multiple conditions.

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.

MySql CASE StatementIn 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!