How to use IF condition in MySql with a select statement. In this tutorial, You are going to learn how to use IF condition in MySql to select column value conditionally.
If Condition in MySql
IF Condition Syntax in MySql
Let’s first see the syntax of MySql if condition.
|
1 2 3 |
// Syntax if (condition, print value if true, print value if false) |
For better understanding, Let’s start with simple examples.
|
1 2 |
// If timestamp column is null then print 1 otherwise 0. select if(timestamp is NULL,1,0) from table_name; |
In the above query, we print 1 if the value of timestamp column is NULL otherwise 0.
Let’s take some more examples.
|
1 2 3 |
SELECT IF(1 = 3,'true','false'); -- print false SELECT IF(2 = 2,' true','false'); -- print true |
How to compare NULL values in MySql
Difference between inner join and left join
NOTE – There is another IF statement in MySql version 5.6 which is different from if() function.
If Condition in MySql – Query Examples
To understand it’s proper usage. Let’s create a table emp_data.
|
1 2 3 4 5 6 7 8 9 |
+--------+--------+-------------------+ | emp_id | name | designation | +--------+--------+-------------------+ | 1 | John | Manager | | 2 | smith | Sales Associate | | 3 | Amit | Software Engineer | | 4 | vikash | Customer Support | | 5 | vikky | Manager | +--------+--------+-------------------+ |
Let’s print the name and designation of all the employees. We’ll print the designation either Manager or Other.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select name,if(designation='Manager','Manager','Other') as designation from emp_data; Output - +--------+-------------+ | name | designation | +--------+-------------+ | John | Manager | | smith | Other | | Amit | Other | | vikash | Other | | vikky | Manager | +--------+-------------+ |
If Condition in MySql with Aggregate Functions
Let’s check another example with an aggregate function. To demonstrate this example I am creating the table products.
|
1 2 3 4 5 6 7 8 9 10 |
+------------+--------------+--------+ | product_id | product_name | status | +------------+--------------+--------+ | 1 | Nokia | A | | 2 | cooker | P | | 3 | HTC | A | | 4 | Mixer | D | | 5 | Laptop | D | | 6 | Reebok shoes | A | +------------+--------------+--------+ |
Let’s write a query which will print the count of active, disabled and pending products.
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT SUM(IF(status = 'A',1,0)) AS Active, -> SUM(IF(status = 'D',1,0)) AS Disabled, -> SUM(IF(status = 'P',1,0)) AS Pending -> FROM products; +--------+----------+---------+ | Active | Disabled | Pending | +--------+----------+---------+ | 3 | 2 | 1 | +--------+----------+---------+ |
You can achieve the same result through group by clause without using IF condition.
|
1 2 3 4 5 6 7 8 |
mysql> select count(status),status from products group by status; +---------------+--------+ | count(status) | status | +---------------+--------+ | 3 | A | | 2 | D | | 1 | P | +---------------+--------+ |
Difference between Inner Join and Left Join
For more reference:
MySql Recommended Books
