{"id":511,"date":"2011-05-09T13:08:57","date_gmt":"2011-05-09T13:08:57","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=511"},"modified":"2020-04-11T02:02:38","modified_gmt":"2020-04-11T09:02:38","slug":"mysql-if-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-control-flow-functions\/mysql-if-function\/","title":{"rendered":"MySQL IF Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL IF function<\/strong> that returns a value based on a given condition.<\/p>\n\n\n\n

Introduction to MySQL IF function<\/h2>\n\n\n\n

MySQL IF<\/code> function is one of the MySQL control flow functions that returns a value based on a condition. The IF<\/code> function is sometimes referred to as IF ELSE<\/code> or IF THEN ELSE<\/code> function.<\/p>\n\n\n\n

The syntax of the MySQL IF<\/code> function is as follows:<\/p>\n\n\n

IF(expr,if_true_expr,if_false_expr)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the expr<\/code> evaluates to TRUE<\/code> i.e., expr<\/code> is not NULL<\/code> and expr<\/code> is not 0, the IF<\/code> function returns the if_true_expr<\/code> , otherwise, it returns if_false_expr<\/code> The IF<\/code> function returns a numeric or a string, depending on how it is used.<\/p>\n\n\n\n
The IF function that we cover in this tutorial is different from the IF statement<\/a>.<\/p>\n\n\n\n
MySQL IF function Examples<\/h2>\n\n\n\n
Let\u2019s practice with several examples to see how the MySQL IF function works.<\/p>\n\n\n\n
A simple IF function example<\/h3>\n\n\n\n
You can use the IF function directly in the SELECT statement<\/a> without the FROM<\/code> and other clauses as follows:<\/p>\n\n\n
SELECT<\/span> IF<\/span>(1<\/span> = 2<\/span>,'true'<\/span>,'false'<\/span>); -- false<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> IF<\/span>(1<\/span> = 1<\/span>,' true'<\/span>,'false'<\/span>); -- true<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Displaying N\/A instead of NULL using MySQL IF function<\/h3>\n\n\n\n
Let’s take a look at the data in the customers<\/code> table in the sample database<\/a>.<\/p>\n\n\n\n
In the customers<\/code> table, many customers do not have state data in the state<\/code> column therefore when we select customers, the state column displays NULL<\/code> values, which is not meaningful for the reporting purpose. See the following query:<\/p>\n\n\n
SELECT<\/span> \n    customerNumber, customerName, state, country\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
We can improve the output by using the IF<\/code> function to return N\/A if the state is NULL as the following query:<\/p>\n\n\n
SELECT<\/span> \n    customerNumber,\n    customerName,\n    IF<\/span>(state IS<\/span> NULL<\/span>, 'N\/A'<\/span>, state) state,\n    country\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL IF function with aggregate functions<\/h2>\n\n\n\n
MySQL SUM IF – Combining the IF function with the SUM function<\/h3>\n\n\n\n
The IF<\/code> function is useful when it combines with an aggregate function. Suppose if you want to know how many orders have been shipped and cancelled, you can use the IF function with the SUM aggregate function<\/a> as the following query:<\/p>\n\n\n
SELECT<\/span> \n    SUM<\/span>(IF<\/span>(status<\/span> = 'Shipped'<\/span>, 1<\/span>, 0<\/span>)) AS<\/span> Shipped,\n    SUM<\/span>(IF<\/span>(status<\/span> = 'Cancelled'<\/span>, 1<\/span>, 0<\/span>)) AS<\/span> Cancelled\nFROM<\/span>\n    orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In the query above, if the order status is shipped<\/code> or cancelled<\/code> ,<\/em> the IF function returns 1 otherwise it returns 0. The SUM<\/code> function calculates the total number of shipped<\/code> and cancelled<\/code> orders based on the returned value of the IF<\/code> function.<\/p>\n\n\n\n
MySQL COUNT IF – Combining the IF function with the COUNT function<\/h3>\n\n\n\n
First, we select distinct<\/a> order’s status in the orders<\/code> table using the following query:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n    status<\/span>\nFROM<\/span>\n    orders\nORDER<\/span> BY<\/span> status<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Order<\/figure>\n\n\n\n
Second, we can get the number of orders in each status by combining the IF<\/code> function with the COUNT<\/code> function. Because the COUNT<\/code> function does not count NULL<\/code> values, the IF<\/code> function returns NULL<\/code> if the status is not in the selected status, otherwise it returns 1. See the following query:<\/p>\n\n\n
SELECT<\/span> \n    COUNT<\/span>(IF<\/span>(status<\/span> = 'Cancelled'<\/span>, 1<\/span>, NULL<\/span>)) Cancelled,\n    COUNT<\/span>(IF<\/span>(status<\/span> = 'Disputed'<\/span>, 1<\/span>, NULL<\/span>)) Disputed,\n    COUNT<\/span>(IF<\/span>(status<\/span> = 'In Process'<\/span>, 1<\/span>, NULL<\/span>)) 'In Process'<\/span>,\n    COUNT<\/span>(IF<\/span>(status<\/span> = 'On Hold'<\/span>, 1<\/span>, NULL<\/span>)) 'On Hold'<\/span>,\n    COUNT<\/span>(IF<\/span>(status<\/span> = 'Resolved'<\/span>, 1<\/span>, NULL<\/span>)) 'Resolved'<\/span>,\n    COUNT<\/span>(IF<\/span>(status<\/span> = 'Shipped'<\/span>, 1<\/span>, NULL<\/span>)) 'Shipped'<\/span>\nFROM<\/span>\n    orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Of course, you can achieve the similar result using the GROUP BY clause<\/a> and the COUNT<\/code> function without using the IF<\/code> function as the following query:<\/p>\n\n\n
SELECT<\/span> status<\/span>, COUNT<\/span>(STATUS<\/span>)\nFROM<\/span> orders\nGROUP<\/span> BY<\/span> status<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, we have introduced you to the MySQL IF<\/code> function which helps you write a query with condition embedded in the SELECT<\/code> clause.<\/p>\n\n\n\n
Reference<\/h2>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n