w3resource

MySQL IF() function

IF() function

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);

Parameters

Name Description Return Type
expression An expression.  
expr_true Returns when the condition is TRUE. a string when expr_true is a string, a floating-point value when expr _true is a floating-point value and an integer when expr _true is an integer.
expr_false Returns when the condition is FALSE. a string when expr_false is a string, a floating-point value when expr _false is a floating-point value and an integer when expr _false is an integer.

MySQL Version: 5.6

Note: There is an another IF statement, which differs from the IF() function described in MySQL procedure chapter.

Pictorial Presentation

MYSQL IF function pictorial presentation

Example : MySQL IF() function

In the following statement, since 1 is less than 3, so the IF() returns the third expression, i.e. false.

Code:

SELECT IF(1>3,'true','false');

Sample Output:

mysql> SELECT IF(1>3,'true','false');
+------------------------+
| IF(1>3,'true','false') |
+------------------------+
| false                  | 
+------------------------+
1 row in set (0.00 sec)

Example : IF() function with CASE

In the following example the MySQL statement returns the third expression 'false' since the first expression is not true.

Code:

SELECT IF((SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END),'true','false');

Sample Output:

mysql> SELECT IF((SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END),'true','false');
+------------------------------------------------------------------------+
| IF((SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END),'true','false') |
+------------------------------------------------------------------------+
| false                                                                  | 
+------------------------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

MySQL IF in SELECT statement

The following MySQL statement returns the book name and in which language the book have been published. The status of language is English Book for pub_lang English other wise it returns 'Other Language'.

Code:

SELECT book_name,
IF(pub_lang='English',"English Book","Other Language") 
AS Language 
FROM book_mast;

Sample table: book_mast


Sample Output:

mysql> SELECT book_name,
    -> IF(pub_lang="English", "Engllish Book", "Other Lnaguage")
    -> AS Language
    -> FROM book_mast;
+-------------------------------------+----------------+
| book_name                           | Language       |
+-------------------------------------+----------------+
| Introduction to Electrodynamics     | Engllish Book  | 
| Understanding of Steel Construction | Engllish Book  | 
| Guide to Networking                 | Other Lnaguage | 
| Transfer  of Heat and Mass          | Engllish Book  | 
| Conceptual Physics                  | Other Lnaguage | 
| Fundamentals of Heat                | Other Lnaguage | 
| Advanced 3d Graphics                | Other Lnaguage | 
| Human Anatomy                       | Other Lnaguage | 
| Mental Health Nursing               | Engllish Book  | 
| Fundamentals of Thermodynamics      | Engllish Book  | 
| The Experimental Analysis of Cat    | Other Lnaguage | 
| The Nature  of World                | Engllish Book  | 
| Environment a Sustainable Future    | Other Lnaguage | 
| Concepts in Health                  | Other Lnaguage | 
| Anatomy & Physiology                | Other Lnaguage | 
| Networks and Telecommunications     | Other Lnaguage | 
+-------------------------------------+----------------+
16 rows in set (0.02 sec)

MySQL SELECT within IF statement

The following MySQL statement returns the book name, isbn no and a column alias Page/Price of an expression from the book_mast table. The Page/Price will be Price when the number of other language than English is more than the language English other wise the Page/Price will be Pages and no_page. Here in this example number of other language books is more than the number of English books .

Code:

SELECT book_name,isbn_no,
IF((SELECT COUNT(*) FROM book_mast WHERE pub_lang='English')>
(SELECT COUNT(*) FROM book_mast WHERE pub_lang<>'English'),
(CONCAT("Pages: ",no_page)),(CONCAT("Price: ",book_price))) 
AS "Page / Price"
FROM book_mast;

Sample table: book_mast


Sample Output:

mysql> SELECT book_name,isbn_no,
    -> IF((SELECT COUNT(*) FROM book_mast WHERE pub_lang='English')>
    -> (SELECT COUNT(*) FROM book_mast WHERE pub_lang<>'English'),
    -> (CONCAT("Pages: ",no_page)),(CONCAT("Price: ",book_price))) 
    -> AS "Page / Price"
    -> FROM book_mast;
+-------------------------------------+-------------+---------------+
| book_name                           | isbn_no     | Page / Price  |
+-------------------------------------+-------------+---------------+
| Introduction to Electrodynamics     | 0000979001  | Price: 85.00  | 
| Understanding of Steel Construction | 0000979002  | Price: 105.50 | 
| Guide to Networking                 | 0000979003  | Price: 200.00 | 
| Transfer  of Heat and Mass          | 0000979004  | Price: 250.00 | 
| Conceptual Physics                  | 0000979005  | Price: 145.00 | 
| Fundamentals of Heat                | 0000979006  | Price: 112.00 | 
| Advanced 3d Graphics                | 0000979007  | Price: 56.00  | 
| Human Anatomy                       | 0000979008  | Price: 50.50  | 
| Mental Health Nursing               | 0000979009  | Price: 145.00 | 
| Fundamentals of Thermodynamics      | 0000979010  | Price: 225.00 | 
| The Experimental Analysis of Cat    | 0000979011  | Price: 95.00  | 
| The Nature  of World                | 0000979012  | Price: 88.00  | 
| Environment a Sustainable Future    | 0000979013  | Price: 100.00 | 
| Concepts in Health                  | 0000979014  | Price: 180.00 | 
| Anatomy & Physiology                | 0000979015  | Price: 135.00 | 
| Networks and Telecommunications     | 00009790_16 | Price: 45.00  | 
+-------------------------------------+-------------+---------------+
16 rows in set (0.00 sec)

Displaying customize text instead of NULL using MySQL IF function

In the book_mast table, the publishing language of some book have not been set, therefore when we select book_mast, the pub_lang column displays NULL values, which is not meaningful for the reporting purpose. See the following query:

 SELECT book_id, book_name, pub_lang
    -> FROM book_mast;
+---------+-------------------------------------+----------+
| book_id | book_name                           | pub_lang |
+---------+-------------------------------------+----------+
| BK001   | Introduction to Electrodynamics     | English  |
| BK002   | Understanding of Steel Construction | English  |
| BK003   | Guide to Networking                 | Hindi    |
| BK004   | Transfer  of Heat and Mass          | English  |
| BK005   | Conceptual Physics                  | NULL     |
| BK006   | Fundamentals of Heat                | German   |
| BK007   | Advanced 3d Graphics                | Hindi    |
| BK008   | Human Anatomy                       | German   |
| BK009   | Mental Health Nursing               | English  |
| BK010   | Fundamentals of Thermodynamics      | English  |
| BK011   | The Experimental Analysis of Cat    | French   |
| BK012   | The Nature  of World                | English  |
| BK013   | Environment a Sustainable Future    | German   |
| BK014   | Concepts in Health                  | NULL     |
| BK015   | Anatomy & Physiology                | Hindi    |
| BK016   | Networks and Telecommunications     | French   |
+---------+-------------------------------------+----------+
16 rows in set (0.00 sec)

We can avoid displaying NULL in the output by using the IF function to return N/A instead of NULL. Here is query below.

Code:

SELECT book_id, book_name, 
IF(pub_lang IS NULL,'N/A',pub_lang) AS "Pub. Language"
FROM book_mast;

Sample Output:

mysql> SELECT book_id, book_name,
    ->        IF(pub_lang IS NULL,'N/A',pub_lang) AS "Pub. Language"
    ->        FROM book_mast;
+---------+-------------------------------------+---------------+
| book_id | book_name                           | Pub. Language |
+---------+-------------------------------------+---------------+
| BK001   | Introduction to Electrodynamics     | English       |
| BK002   | Understanding of Steel Construction | English       |
| BK003   | Guide to Networking                 | Hindi         |
| BK004   | Transfer  of Heat and Mass          | English       |
| BK005   | Conceptual Physics                  | N/A           |
| BK006   | Fundamentals of Heat                | German        |
| BK007   | Advanced 3d Graphics                | Hindi         |
| BK008   | Human Anatomy                       | German        |
| BK009   | Mental Health Nursing               | English       |
| BK010   | Fundamentals of Thermodynamics      | English       |
| BK011   | The Experimental Analysis of Cat    | French        |
| BK012   | The Nature  of World                | English       |
| BK013   | Environment a Sustainable Future    | German        |
| BK014   | Concepts in Health                  | N/A           |
| BK015   | Anatomy & Physiology                | Hindi         |
| BK016   | Networks and Telecommunications     | French        |
+---------+-------------------------------------+---------------+
16 rows in set (0.01 sec)

MySQL IF function with aggregate functions

MySQL SUM IF – IF function with the SUM function

Sample table: purchase


The following SQL statement will display the number of books purchase which publish by the language English and Non-English in a row.

Code:

SELECT SUM(IF(pub_lang = 'English',1,0))   AS English,
       SUM(IF(pub_lang <> 'English',1,0)) AS "Non English"
FROM purchase;

Sample Output:

mysql> SELECT SUM(IF(pub_lang = 'English',1,0))   AS English,
    ->        SUM(IF(pub_lang <> 'English',1,0)) AS "Non English"
    -> FROM purchase;
+---------+-------------+
| English | Non English |
+---------+-------------+
|       4 |           1 |
+---------+-------------+
1 row in set (0.02 sec)

MySQL COUNT IF – IF function with the COUNT function

Sample table: publisher


The following sql statement will display the number of publishers in a row for each country listed in publisher table.

Code:

SELECT COUNT(IF(country = 'USA',1,NULL))  USA,
       COUNT(IF(country = 'UK',1,NULL))  UK,
       COUNT(IF(country = 'India',1,NULL))  India,
       COUNT(IF(country = 'Australia',1,NULL))  Australia
FROM publisher;

Sample Output:

mysql> SELECT COUNT(IF(country = 'USA',1,NULL))  USA,
    ->        COUNT(IF(country = 'UK',1,NULL))  UK,
    ->        COUNT(IF(country = 'India',1,NULL))  India,
    ->        COUNT(IF(country = 'Australia',1,NULL))  Australia
    -> FROM publisher;
+-----+----+-------+-----------+
| USA | UK | India | Australia |
+-----+----+-------+-----------+
|   3 |  2 |     2 |         1 |
+-----+----+-------+-----------+
1 row in set (0.00 sec)

Another way to achieve the similar result you can use the GROUP BY clause and the COUNT function without using the IF function, the display report is quite different. The above output have shown in a row but the following query the will display the number of rows for distinct number of countries. Here is the code and result :

mysql> SELECT country, COUNT(country)
    -> FROM publisher
    -> GROUP BY country;
+-----------+----------------+
| country   | COUNT(country) |
+-----------+----------------+
| Australia |              1 |
| India     |              2 |
| UK        |              2 |
| USA       |              3 |
+-----------+----------------+
4 rows in set (0.01 sec)

Previous: CASE operator
Next: IFNULL()



Inviting useful, relevant, well-written and unique guest posts