MySQL IF() function

Last update on July 11 2024 09:52:04 (UTC/GMT +8 hours)

IF() function

Introduction to MySQL IF() Function:

The IF() function in MySQL is a powerful tool that allows you to implement conditional logic directly within your SQL queries. It takes three parameters:

a condition to evaluate,
a value to return if the condition is true,
and a value to return if the condition is false.

This makes it incredibly versatile for dynamically selecting values based on specific criteria within your data.

This function is useful in -

It allows you to implement conditional logic directly within a query.

This is invaluable for handling different scenarios and producing different results based on specified conditions.

You can use IF() to dynamically select different columns based on specific conditions.

It's used to validate data before inserting or updating a table.

By using IF(), you can check conditions and ensure that only valid data is processed.

IF() allows you to set up contingency plans in case certain conditions are not met.

When used in conjunction with aggregate functions like SUM() or COUNT(), IF() can selectively include or exclude certain records from the calculation.

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: 8.0

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:


-- This SQL statement uses the IF function to return a specific value based on the evaluation of a condition
-- Explanation: The query evaluates the condition '1 > 3' and returns 'true' if the condition is true, otherwise it returns 'false'.
SELECT 
    IF(1 > 3, 'true', 'false');  -- The IF function checks if the condition '1 > 3' is true; if it is, it returns 'true', otherwise it returns 'false'

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The IF function in MySQL allows for conditional logic to return specific results based on the evaluation of a condition.

In this specific IF function:

IF(1 > 3, 'true', 'false'): The IF function takes three arguments:

The first argument is the condition to evaluate (1 > 3).

The second argument is the value to return if the condition is true ('true').

The third argument is the value to return if the condition is false ('false').

Since the condition 1 > 3 is false, the IF function returns 'false'.

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:


-- This SQL statement uses the IF function along with a subquery that contains a CASE expression to return a specific value based on the evaluation of conditions
-- Explanation: The query first evaluates the CASE expression to return 'true' or 'false', then uses the IF function to return 'true' or 'false' based on the result of the subquery.
SELECT 
    IF(  -- The IF function begins
        (  -- Start of the subquery
            SELECT 
                CASE  -- The CASE expression begins
                    WHEN 1 > 0 THEN 'true'  -- If the condition '1 > 0' is true, return the string 'true'
                    ELSE 'false'  -- If the condition is not true, return the string 'false'
                END  -- End of the CASE expression
        ),  -- End of the subquery
        'true',  -- If the subquery result is true (non-zero and non-null), return 'true'
        'false'  -- If the subquery result is false (zero or null), return 'false'
    );  -- End of the IF function

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The IF function in MySQL allows for conditional logic to return specific results based on the evaluation of a condition.

This specific query involves two parts:

A subquery that contains a CASE expression:

SELECT CASE WHEN 1 > 0 THEN 'true' ELSE 'false' END:

The CASE expression checks the condition 1 > 0.

Since 1 > 0 is true, the CASE expression returns 'true'.

The IF function:

IF(..., 'true', 'false'):

The first argument is the result of the subquery ('true' in this case).

The second argument ('true') is the value to return if the first argument (the subquery result) is true.

The third argument ('false') is the value to return if the first argument (the subquery result) is false.

Since the subquery returns 'true', which is a non-zero and non-null value, the IF function returns 'true'.

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)

Differences Between IF() and CASE

While both IF() and CASE statements can handle conditional logic, they have distinct uses. The IF() function is more compact and is suitable for simple, binary conditions. In contrast, the CASE statement is more versatile for handling multiple conditions or complex scenarios.

MySQL IF in SELECT statement

The following MySQL statement returns the book name and the language status of each book. If the book is published in English, it returns "English Book"; otherwise, it returns "Other Language".

Sample table: book_mast

Code:


-- This SQL statement selects the book name and determines if the book is in English or another language
-- Explanation: The query retrieves book names and uses the IF function to classify the language of the book as either 'English Book' or 'Other Language'.
SELECT 
    book_name,  -- Select the book name from the table
    IF(pub_lang = 'English', "English Book", "Other Language")  -- Use the IF function to check if the publication language is 'English'
    AS Language  -- Assign an alias 'Language' to the result of the IF function
FROM 
    book_mast;  -- Specify the table 'book_mast' to retrieve data from

Explanation:

The SELECT statement is used to retrieve specific columns from the table.

The query involves selecting data from the book_mast table and includes the following elements:

book_name: Selects the column book_name from the book_mast table, which contains the names of the books.

IF(pub_lang = 'English', "English Book", "Other Language"):

The IF function checks the condition pub_lang = 'English'.

If the condition is true (i.e., the book's language is English), it returns "English Book".

If the condition is false (i.e., the book's language is not English), it returns "Other Language".

AS Language: The result of the IF function is given the alias Language to represent the classification of the book's language in the output.

The query essentially classifies each book in the book_mast table as either an "English Book" or "Other Language" based on the value of the pub_lang column.

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 number, and a column alias "Page / Price" from the book_mast table. This column will display "Pages: [no_page]" if the number of English books is greater than the number of non-English books; otherwise, it will display "Price: [book_price]". Explain how this is determined using the provided code. Here in this example number of other language books is more than the number of English books.

Sample table: book_mast

Code:


-- This SQL statement selects book details and conditionally shows the number of pages or the price based on the comparison of English and non-English books
-- Explanation: The query retrieves book names, ISBN numbers, and conditionally displays either the page count or the price based on the comparison of the count of English and non-English books.
SELECT 
    book_name,  -- Select the book name from the table
    isbn_no,  -- Select the ISBN number from the table
    IF(  -- Begin the IF function to check a condition
        (SELECT COUNT(*) FROM book_mast WHERE pub_lang = 'English') >  -- Subquery to count the number of English books
        (SELECT COUNT(*) FROM book_mast WHERE pub_lang <> 'English'),  -- Subquery to count the number of non-English books
        (CONCAT("Pages: ", no_page)),  -- If the count of English books is greater, concatenate 'Pages: ' with the number of pages
        (CONCAT("Price: ", book_price))  -- If the count of English books is not greater, concatenate 'Price: ' with the book price
    ) AS "Page / Price"  -- Assign an alias 'Page / Price' to the result of the IF function
FROM 
    book_mast;  -- Specify the table 'book_mast' to retrieve data from

Explanation:

The SELECT statement is used to retrieve specific columns from the table book_mast.

The query involves selecting book details and includes the following elements:

book_name: Selects the column book_name from the book_mast table, which contains the names of the books.

isbn_no: Selects the column isbn_no from the book_mast table, which contains the ISBN numbers of the books.

IF(...):

The IF function checks a condition based on the comparison of counts from two subqueries.

(SELECT COUNT(*) FROM book_mast WHERE pub_lang = 'English'): Subquery to count the number of books where the publication language is English.

(SELECT COUNT(*) FROM book_mast WHERE pub_lang <> 'English'): Subquery to count the number of books where the publication language is not English.

If the count of English books is greater than the count of non-English books:

CONCAT("Pages: ", no_page): Concatenates the string "Pages: " with the number of pages (no_page) of the book.

If the count of English books is not greater than the count of non-English books:

CONCAT("Price: ", book_price): Concatenates the string "Price: " with the price (book_price) of the book.

AS "Page / Price": The result of the IF function is given the alias "Page / Price" to represent the conditional display of either the number of pages or the price in the output.

The query essentially provides book details and conditionally shows either the page count or the price of each book based on the comparison of the total number of English books versus non-English books in the book_mast table.

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:

Output:

mysql> 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:


-- This SQL statement selects book details and conditionally displays the publication language or 'N/A' if the language is null
-- Explanation: The query retrieves book IDs, book names, and the publication language, replacing null values with 'N/A'.
SELECT 
    book_id,  -- Select the book ID from the table
    book_name,  -- Select the book name from the table
    IF(pub_lang IS NULL, 'N/A', pub_lang) AS "Pub. Language"  -- Use the IF function to check if the publication language is null
    -- If the publication language is null, return 'N/A', otherwise return the actual publication language
FROM 
    book_mast;  -- Specify the table 'book_mast' to retrieve data from

Explanation:

The SELECT statement is used to retrieve specific columns from the table book_mast.

The query involves selecting book details and includes the following elements:

book_id: Selects the column book_id from the book_mast table, which contains the IDs of the books.

book_name: Selects the column book_name from the book_mast table, which contains the names of the books.

IF(pub_lang IS NULL, 'N/A', pub_lang) AS "Pub. Language":

The IF function checks if the pub_lang column is NULL.

IF(pub_lang IS NULL, 'N/A', pub_lang):

The first argument is the condition to evaluate (pub_lang IS NULL).

The second argument ('N/A') is the value to return if the condition is true (i.e., if pub_lang is NULL).

The third argument (pub_lang) is the value to return if the condition is false (i.e., if pub_lang is not NULL).

The result of the IF function is given the alias "Pub. Language" to represent the publication language in the output.

The query essentially provides book details and conditionally shows either the actual publication language or 'N/A' if the publication language is NULL in the book_mast table.

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 calculates and displays the total number of books purchased, categorized by whether they are published in English or in a language other than English. Explain how this is achieved using the provided code.

Code:


-- This SQL statement calculates the number of English and non-English purchases
-- Explanation: The query sums up the number of purchases where the publication language is English and where it is not English.
SELECT 
    SUM(IF(pub_lang = 'English', 1, 0)) AS English,  -- Sum up 1 for each row where pub_lang is 'English', giving the total count of English purchases
    SUM(IF(pub_lang <> 'English', 1, 0)) AS "Non English"  -- Sum up 1 for each row where pub_lang is not 'English', giving the total count of non-English purchases
FROM 
    purchase;  -- Specify the table 'purchase' to retrieve data from

Explanation:

The SELECT statement is used to retrieve aggregated data from the table purchase.

The query involves calculating the number of purchases based on the publication language and includes the following elements:

SUM(IF(pub_lang = 'English', 1, 0)) AS English:

IF(pub_lang = 'English', 1, 0): The IF function checks if pub_lang is 'English'.

If the condition is true (i.e., pub_lang is 'English'), it returns 1.

If the condition is false (i.e., pub_lang is not 'English'), it returns 0.

SUM(IF(pub_lang = 'English', 1, 0)): The SUM function adds up the values returned by the IF function for each row, resulting in the total count of English purchases.

AS English: The result of the SUM function is given the alias English to represent the total count of English purchases in the output.

SUM(IF(pub_lang <> 'English', 1, 0)) AS "Non English":

IF(pub_lang <> 'English', 1, 0): The IF function checks if pub_lang is not 'English'.

If the condition is true (i.e., pub_lang is not 'English'), it returns 1.

If the condition is false (i.e., pub_lang is 'English'), it returns 0.

SUM(IF(pub_lang <> 'English', 1, 0)): The SUM function adds up the values returned by the IF function for each row, resulting in the total count of non-English purchases.

AS "Non English": The result of the SUM function is given the alias "Non English" to represent the total count of non-English purchases in the output.

The query essentially provides a count of purchases where the publication language is English and a count of purchases where the publication language is not English from the purchase table.

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 calculates and displays the total number of publishers, categorized by country, listed in the publisher table. Explain how this is achieved using the provided code.

Code:


-- This SQL statement counts the number of publishers from specific countries
-- Explanation: The query calculates the count of publishers from the USA, UK, India, and Australia using conditional counting.
SELECT 
    COUNT(IF(country = 'USA', 1, NULL)) AS USA,  -- Count the number of rows where the country is 'USA'
    COUNT(IF(country = 'UK', 1, NULL)) AS UK,  -- Count the number of rows where the country is 'UK'
    COUNT(IF(country = 'India', 1, NULL)) AS India,  -- Count the number of rows where the country is 'India'
    COUNT(IF(country = 'Australia', 1, NULL)) AS Australia  -- Count the number of rows where the country is 'Australia'
FROM 
    publisher;  -- Specify the table 'publisher' to retrieve data from

Explanation:

The SELECT statement is used to retrieve aggregated data from the table publisher.

The query involves counting the number of publishers from specific countries and includes the following elements:

COUNT(IF(country = 'USA', 1, NULL)) AS USA:

IF(country = 'USA', 1, NULL): The IF function checks if country is 'USA'.

If the condition is true (i.e., country is 'USA'), it returns 1.

If the condition is false (i.e., country is not 'USA'), it returns NULL.

COUNT(IF(country = 'USA', 1, NULL)): The COUNT function counts the number of non-NULL values returned by the IF function, resulting in the total count of publishers from the USA.

AS USA: The result of the COUNT function is given the alias USA to represent the total count of publishers from the USA in the output.

COUNT(IF(country = 'UK', 1, NULL)) AS UK:

IF(country = 'UK', 1, NULL): The IF function checks if country is 'UK'.

If the condition is true (i.e., country is 'UK'), it returns 1.

If the condition is false (i.e., country is not 'UK'), it returns NULL.

COUNT(IF(country = 'UK', 1, NULL)): The COUNT function counts the number of non-NULL values returned by the IF function, resulting in the total count of publishers from the UK.

AS UK: The result of the COUNT function is given the alias UK to represent the total count of publishers from the UK in the output.

COUNT(IF(country = 'India', 1, NULL)) AS India:

IF(country = 'India', 1, NULL): The IF function checks if country is 'India'.

If the condition is true (i.e., country is 'India'), it returns 1.

If the condition is false (i.e., country is not 'India'), it returns NULL.

COUNT(IF(country = 'India', 1, NULL)): The COUNT function counts the number of non-NULL values returned by the IF function, resulting in the total count of publishers from India.

AS India: The result of the COUNT function is given the alias India to represent the total count of publishers from India in the output.

COUNT(IF(country = 'Australia', 1, NULL)) AS Australia:

IF(country = 'Australia', 1, NULL): The IF function checks if country is 'Australia'.

If the condition is true (i.e., country is 'Australia'), it returns 1.

If the condition is false (i.e., country is not 'Australia'), it returns NULL.

COUNT(IF(country = 'Australia', 1, NULL)): The COUNT function counts the number of non-NULL values returned by the IF function, resulting in the total count of publishers from Australia.

AS Australia: The result of the COUNT function is given the alias Australia to represent the total count of publishers from Australia in the output.

The query essentially provides the counts of publishers from the USA, UK, India, and Australia in the publisher table.

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)

Frequently Asked Questions (FAQ) - MySQL IF() Function

1. What is the MySQL IF() function?

The MySQL IF() function is a tool that allows you to perform conditional logic directly in SQL queries, returning different results based on specified conditions.

2. How does the MySQL IF() function work?

It evaluates a given condition and returns one value if the condition is true and another value if the condition is false.

3. Why should we use the MySQL IF() function?

The IF() function is invaluable for dynamically selecting values, validating data, handling different scenarios, and setting up contingency plans within SQL queries.

4. In what scenarios is the MySQL IF() function useful?

You can use it to:

Dynamically select different columns based on conditions.

Validate data before inserting or updating tables.

Handle different outcomes in aggregate functions like SUM() or COUNT().

5. Can the MySQL IF() function be used with other functions?

Yes, it can be combined with aggregate functions and other SQL features to produce complex conditional results.

6. How does MySQL IF() function differ from the CASE statement?

The IF() function is suited for simple, binary conditions, whereas the CASE statement is more versatile and can handle multiple conditions and more complex scenarios.

7. How can the MySQL IF() function help in dealing with NULL values?

It can be used to replace NULL values with meaningful text, improving the readability of query results.

Previous: CASE operator
Next: IFNULL()