IF in SELECT statement MySQL

‘IF’ in ‘SELECT’ statements is used to get the output based on a column value. This article will see how to get output in ‘SELECT’ statements based on ‘IF’ in a MySQL query.

Table of Contents

1. ‘IF’ with ‘SELECT’ using – IF() function
2. ‘IF’ with ‘SELECT’ using – case
3. SELECT with UNION

Before moving into the details of each, let us prepare the data to be used across all examples. We will be creating a table customer_sale_data followed by inserting rows to it.

CREATE TABLE customer_sale_data (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    sex VARCHAR(50),
    purchases INT,
    primary key (customer_id)
);
 INSERT INTO customer_sale_data (customer_name, sex, purchases) 
 VALUES("Henry","Male",2300),
 ("Henric","Male",2400),
 ("Sandy","Female",2598),
 ("Richa","Female",3000),
 ("Jennifer","Female",5000),
 ("Rosy","Female",6700),
 ("Ved","Male",2303),
 ("Thomas","Male",7899),
 ("Rebecca","Female",2007),
 ("George","Male",5656),
 ("Will","Male",9000),
 ("Karan","Male",10000);

Let us see what got into the customer_sale_data table by executing:

SELECT * FROM customer_sale_data;

Output:-

‘IF’ with ‘SELECT’ using – IF() function

In this section, we will be using the SELECT with IF() function.

Syntax:-

SELECT column1,column2,…| ALL ... , IF(<condition> , value_if_condition_true ,value_if_condition_false) FROM table_references; 

• column1, column2,…| ALL – is the list of fields selected.
• IF() – If function takes three parameters
• First is the condition to be checked.
• Second is the value_if_condition_true – the value to be part of SELECT statement if the first parameter’s condition is true.
• The third is the value_if_condition_false – the value to be part of the SELECT statement if the first parameter’s condition is false.
• table_references – specifies the references of the table, which are the source of the selection.

Example:-

There should be an extra column in the output to display the customer_category. If the customer purchases are equal to or more than 5000, he/she should be considered a gold customer.

SELECT 
    *,
    IF(purchases >= 5000,
        'Gold_Customers',
        '') AS customer_categorization
FROM
    customer_sale_data;

Action Output Message Response:-

12 row(s) returned.

Output:-

Explanation:-

figure 2 shows an additional column customer_categorization—the customers with a value of more than 5000 in the purchases column are GOLD customers.

In the query, IF() function is used with a condition to be checked purchases>=5000. If the condition is true, Gold_Customers will be the value for the customer_categorization column. Otherwise, the value is blank.

‘IF’ with ‘SELECT’ using – case

In this section, we will see how and where to use the case. CASE is used when there are many choices, and different value needs to be returned for other conditions if met true. 

Syntax:-

SELECT column1,column2,…| ALL ... , 
CASE
        WHEN <condition 1>
        WHEN <condition 2>
        ........
        ........
        ELSE <condition n>
    END 
FROM table_references;

Example:- We would like to categorize customers as GOLD if the purchases>=5000, SILVER if the purchases>=3000 AND purchases<5000, NORMAL if purchases<3000. 

Observe the below query for the solution.

SELECT 
    *,
    CASE
        WHEN purchases >= 5000 THEN 'GOLD'
        WHEN purchases >= 3000 AND purchases < 5000 THEN 'SILVER'
        ELSE 'NORMAL'
    END AS customer_categorization
FROM
    customer_sale_data
ORDER BY purchases DESC;

Action Output Message Response:-

12 row(s) returned.

Output:-

Explanation:-

The output in figure 3 shows an additional column added customer_categorization, GOLD, SILVER, and NORMAL customers divided based on different conditions as shown in the query.

<div id=”three” style=”padding-top: 100px; margin-top: -100px;”></div>

SELECT with UNION

We can also use UNION with select if the result we need is mutually exclusive. UNION also assists in obtaining IF conditional SELECT.

Read more about UNION

Let us look into the example to have a better understanding of the concept.

Example:-

We will use the same example that is to categorize customers as GOLD, SILVER, and NORMAL. Observe the below query for the solution.

SELECT 
    *, 'GOLD' AS customer_categorization
FROM
    customer_sale_data
WHERE
    purchases >= 5000 
UNION SELECT 
    *, 'SILVER' AS customer_categorization
FROM
    customer_sale_data
WHERE
    purchases >= 3000 AND purchases < 5000 
UNION SELECT 
    *, 'NORMAL' AS customer_categorization
FROM
    customer_sale_data
WHERE
    purchases < 3000
ORDER BY purchases DESC;

Action Output Message Response:-

12 row(s) returned.

Output:-

Explanation:-

The output in figure 4 shows the same results as in figure 3. If we notice the query,

• Three SELECT commands are there.
• First, SELECT fetches the customers with purchases >= 5000.
• The second SELECT fetches the customers with purchases >= 3000 AND purchases < 5000.
• The third SELECT fetches the customers with purchases < 3000.
• UNION is applied between the three select queries.
• The final result is in the descending order of the purchases.

We hope this article helps you understand how to use ‘IF’ with ‘SELECT.’ Good Luck!!!