How To Use MySQL IF Statement In Select Query

This tutorial explains the usage of MySQL IF and IF ELSE Statements in Select queries with syntax and practical programming examples:

MySQL provides an IF() function which is a flow control function and depending on the condition specified and its evaluation to true or false, the rest of the statement executes.

We will discuss various examples and applications of the IF function.

We will also learn about using IF-ELSE conditional construct through MySQL STORED PROCEDURES and FUNCTIONS and how the created functions can be used with SELECT queries.

=> Click here for the complete MySQL tutorial series

MySQL IF Statement

Syntax:

SELECT IF(condition, value_true, value_false) AS [column_name]

Let’s try to understand the syntax in detail (here we are using SELECT query with IF function)

• condition: It is the conditional statement that we want to evaluate. It can involve single or multiple columns. For example: Checking whether the value against the column is > 100. Here, we can write a condition if column_name > 100
• value_true: This is the String value which we would like to get displayed against the condition if it’s evaluated to true.
• value_false: This is the String value that would get displayed when the condition evaluates to false.

SAMPLE DATA:

We will use an Orders table as sample data for writing examples related to the IF ELSE functions in MySQL

ORDERS Table

• order_id – INT
• customer_name – VARCHAR
• city – VARCHAR
• order_total – DECIMAL
• date – DATETIME

//Table creation script
CREATE TABLE `Orders` (
  `order_id` INT NOT NULL,
  `customer_name` VARCHAR(255),
  `city` VARCHAR(255),
  `order_total` DECIMAL(5,2),
  `order_date` VARCHAR(255),
  PRIMARY KEY (order_id)
) 

// Dummy data insertion script
INSERT INTO `Orders` 
(`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES
 (1080,"Nell L. Aguirre","Hanam","109.31","2020-04-11
 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28
 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2020-05-24
 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2020-07-24
 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio 
Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier
 Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree
 Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis
 Contreras","Montluçon","28.15","2020-04-10 05:56:04"),(1088,"Felix Q.
 Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell
 Willis","Quarona","101.34","2020-02-03 02:05:00");
INSERT INTO `orders`
 (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES
 (1090,"Austin T. Casey","Cardiff","108.22","2020-05-06 
11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08
 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12
 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2020-07-09 
18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2020-01-16
 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2020-09-23
 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08
 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2020-07-25
 01:10:52"),(1098,"Ina Rush","Herne","84.31","2020-08-17
 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2020-09-06 12:48:52");

Please refer to images of the tables with the test data as created above.

ORDERS Table

MySQL IF Examples

Simple IF() function

Suppose we want to have an additional display column, like – is_high_value customer, basis on order_total value > 100

So the problem statement can be – display high_value customer details for all Orders after Sept 1st, 2020.

The SELECT query using the IF function is below:

SELECT customer_name, IF(order_total>100,"yes","no") AS is_high_value
 FROM orders where order_date>'2020-09-01';

Let’s suppose we want to filter out just the high_value_customers. We can add the IF statement to the WHERE clause in the above query.

SELECT customer_name FROM orders WHERE IF(order_total>100,"yes","no") = "yes"
AND order_date>'2020-09-01';

Output:

IF() Function With Aggregate Function

Let’s see an example where we use IF along with aggregate functions like SUM, COUNT, etc. Suppose we want to find the sum of all the Orders with city names starting with ‘H’.

SELECT SUM(IF(city LIKE 'H%', order_total, 0)) AS total FROM orders

Output:

Using COUNT With IF To Group Against Ranges

Suppose we want to group the Orders by order_range with a single query. For example, order total between 1-50 are counted as low_value_customers, 50-100 are counted as high_value_customers and greater than 100 are counted as premium_customers.

Using IF() function, we can get all these details with a single query.

SELECT COUNT(IF(order_total<50,1,NULL)) as low_value_customer, 
COUNT(IF(order_total>50 and order_total < 100,1,NULL)) as high_value_customer,
COUNT(IF(order_total>100,1,NULL)) as premium_customer
FROM orders

In the above query, you can see that we have combined IF() function with COUNT, which would add users to the COUNT depending on the segment they fall into.

Output:

MySQL IF ELSE

IF() function can be used independently with MySQL queries directly, however, MySQL IF ELSE is used as a statement as part of the stored procedures or functions.

Let’s see the use of IF-ELSE with the MySQL function.

We will create a function to calculate customer tier depending on the order_total

• If order_total < 50 -> customer_tier => low
• If order_total > 50 and customer_tier < 100 -> customer_tier => high
• If order_total > 150 -> customer_tier => premium

Such a function is created as follows:

USE mysql_ifelse;
DELIMITER //
CREATE FUNCTION CalculateCustomerTier(order_total FLOAT)
  RETURNS VARCHAR(20)
  BEGIN
    DECLARE customer_type VARCHAR(20);
    IF order_total < 50 THEN SET customer_type = 'low';
    ELSEIF order_total > 50 AND order_total<100 THEN SET customer_type = 'high';
    ELSEIF order_total > 100 THEN SET customer_type='premium';
    ELSE SET customer_type = 'unknown<';
    END IF;
    RETURN customer_type;
  END
  // 
  DELIMITER ;

Here, mysql_ifelse is the name of the database we are using to store this function.

>> Click here for more details on creating functions on MySQL.

Let’s now see how we can call this function using a SELECT query on the Orders table

SELECT customer_name, order_total, CalculateCustomerTier(order_total) AS
 tier FROM orders ORDER BY tier;

Output:

As you can see above, the ‘tier’ column gets a value computed from the function ‘CalculateCustomerTier`

An important point to note here is since Functions or Stored Procedures are statically stored within the database – hence if the function is residing in a different database, then while referring to the function in a query, you would need to use the fully qualified name of the function.

Note: About IF Statement vs IF Function in MySQL

IF() Function provided by MySQL is used in generic MySQL queries. For example: Selecting a value based on some condition etc. whereas the IF statement/IF ELSE statements are used in conjunction with STORED PROCEDURES in MySQL for programming conditional constructs in larger functions.

Frequently Asked Questions

Q #1) How do you write an IF ELSE statement in MySQL?

Answer: MySQL IF() function can be used within a query, while the IF-ELSE conditional statement construct is supported to be used through FUNCTIONS or STORED PROCEDURES.

Create a FUNCTION to calculate city tier from a given list of cities:

• If city -> NEW YORK, CHICAGO -> tier1
• If city -> BOSTON, SAN_FRANCISCO -> tier2
• If city -> DETROIT, CLEVELAND -> tier3
• Else -> tier4

  DELIMITER //
CREATE FUNCTION CalculateCityTier(city_name VARCHAR(100))
  RETURNS VARCHAR(20)
  BEGIN
  DECLARE tier VARCHAR(20);
  IF city_name = 'NEW YORK' OR city_name = 'CHICAGO' THEN SET tier = 'tier1';
  ELSEIF city_name = 'BOSTON' OR city_name = 'SAN FRANCISCO' THEN SET tier = 'tier2';
  ELSEIF city_name = 'DETROIT' OR city_name = 'CLEVELAND' THEN SET tier = 'tier3';
  ELSE SET tier = 'tier4';
  END IF;
  RETURN tier;
  END
  // 
  DELIMITER ;

Q #2) How to use if condition in insert query in MySQL?

Answer: IF function cannot be used directly to do conditional insert, however, to achieve a similar effect, you can use WHERE clause while using INSERT with MySQL to perform a conditional INSERT.

Q #3) How to use the IF function with MySQL UPDATE?

Answer: Let’s see how we can use IF for the conditional update of a column depending on what the IF function evaluates to.

Example: Add a new column – customer_tier to the Orders table.

ALTER TABLE orders ADD COLUMN customer_tier VARCHAR(20);

UPDATE the value of customer_tier to ‘high’ for all rows where order_total is between 50 & 100

We will be using the IF condition to do a conditional update as below:

UPDATE ORDERS SET customer_tier = IF(order_total >50 and order_total<100, 'high', NULL)

In the above query, we can see that the customer_tier is set to the value which is evaluated by IF function and is set to ‘high’ when order_total >50 and order_total<100.

Conclusion

In this tutorial, we looked at different ways we can use the IF function as part of the MySQL queries. We also learned to use multiple IF functions within a single query in combination with aggregate functions like COUNT, which could return output depending on the condition specified in the IF function.

In the later part of the tutorial, we discussed creating a MySQL Function using IF-ELSE conditional construct which could then be used as part of the MySQL query and can act on the supplied input value.

The IF() function and IF-ELSE is a powerful construct and is extremely useful for querying and grouping data, which is the first step towards analyzing big sets of data across databases and tables.

Recommended reading =>> How to use MySQL CASE Statement