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-ELSE (1)

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

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';
customer_nameis_high_value
Desiree Buckleyno
Althea C. Townsendno
Britanney D. Pittsno
Ginger A. Rothyes
Dana Rasmussenno

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:

customer_name
Ginger A. Roth

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:

total
193.62

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:

low_value_customerhigh_value_customerpremium_customer
776

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:

customer_nameorder_totaltier
Macey Ingram68.68high
Xavier Gould92.44high
Fletcher H. Moses69.27high
Britanney D. Pitts86.91high
Merritt A. Humphrey50.84high
Ina Rush84.31high
Dana Rasmussen57.83high
Dustin Love29.57low
Judah Frazier28.57low
Jayme H. Blackburn45.98low
Desiree Buckley37.64low
Elvis Contreras28.15low
Felix Q. Whitaker40.79low
Althea C. Townsend44.65low
Nell L. Aguirre109.31premium
Katell Willis101.34premium
Austin T. Casey108.22premium
Dalton Q. Sims104.04premium
Ruby Rivas108.03premium
Ginger A. Roth106.51premium

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.