Mysql: 'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report

I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

 Answers

SELECT id, 
       IF(type = 'P', amount, amount * -1) as amount
FROM report

See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

Additionally, you could handle when the condition is null. In the case of a null amount:

SELECT id, 
       IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

---

SELECT CompanyName, 
    CASE WHEN Country IN ('USA', 'Canada') THEN 'North America'
         WHEN Country = 'Brazil' THEN 'South America'
         ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

---

Most simplest way is to use a IF(). Yes Mysql allows you to do conditional logic. IF function takes 3 params CONDITION, TRUE OUTCOME, FALSE OUTCOME.

So Logic is

if report.type = 'p' 
    amount = amount 
else 
    amount = -1*amount 

SQL

SELECT 
    id, IF(report.type = 'P', abs(amount), -1*abs(amount)) as amount
FROM  report

You may skip abs() if all no's are +ve only

---

You can try this also

 Select id , IF(type=='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount from table

MySQL: Using IF in a WHERE clause

I recently needed to use an IF statment in a WHERE clause with MySQL. This isn't the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.

How IF works

If works like this:

IF(<condition>, <value if true>, <value if false>)

So as an example, the first query below would return 1 and the second 0:

SELECT IF( 'a' = 'a', 1, 0 );
SELECT IF( 'a' = 'b', 1, 0 );

Using IF in a WHERE query

The following example shows how to use IF in a WHERE query.

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', 1, 0) = 1

In the above example, if the value from the column "myfield" matches "somevalue" then the IF function will evaluate to 1. This is then compared with the value 1 which returns true or false for this part of the where condition depending whether the IF function returns 1 or 0.

This simple example would be obviously be better represented like this and would be more efficient because it's not using an IF function:

SELECT ...
WHERE ...
AND myfield = 'somevalue'

Our actual code had a nested IF which simplified the original query and looked something like this:

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', IF(otherfield = 12345, 1, 0), 0) = 1

An alternative to the above without using IF functions would look something like this:

SELECT ...
WHERE ...
AND ( (myfield = 'somevalue' AND otherfield = 12345) OR myfield != 'somevalue' )

But should you use IF in a WHERE clause?

Probably not. It's probably not the most efficient way of doing things. Ideally you should have already filtered with other WHERE statements otherwise it will have to do the IF function on every row in the database.

I've posted this mainly as a reference to show how it can be done. I'll leave whether or not it should be done up to you :)

 

Related posts:

• Selecting substrings with MySQL using LOCATE and SUBSTRING
• MySQL: Delete records in one table that are not in another
• MySQL: Find records in one table that are not in another
• String concatenation with MySQL
• Insert multiple records into MySQL with a single query

Why MySQL does not use an index in the WHERE IF clause?

Because of this setting:

mysql> show global variables like '%indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

The slow queries log keep receiving:

# Time: 120607 16:58:30
# User@Host: xbtit[xbtit] @  [123.30.53.244]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 16006
SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';

Trying to explain this query:

mysql> EXPLAIN SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | xbtit_files | ALL  | NULL          | NULL | NULL    | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

What surprised me is why MySQL not using indexes:

mysql> show index from xbtit_files;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| xbtit_files |          0 | PRIMARY   |            1 | info_hash   | A         |       16006 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | filename  |            1 | filename    | A         |       16006 |     NULL | NULL   | YES  | BTREE      |         |
| xbtit_files |          1 | category  |            1 | category    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | uploader  |            1 | uploader    | A         |          16 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | bin_hash  |            1 | bin_hash    | A         |       16006 |       20 | NULL   |      | BTREE      |         |
| xbtit_files |          1 | ix_sohaid |            1 | soha_id     | A         |       16006 |     NULL | NULL   | YES  | BTREE      |         |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

FORCE INDEX also doesn't work:

mysql> EXPLAIN SELECT * FROM xbtit_files force index (PRIMARY) WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | xbtit_files | ALL  | NULL          | NULL | NULL    | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

Must I split this query into 2 operations?

---

In MySQL, you cannot create indexes on expressions, and the optimizer is not smart enough to split your query against two indexes.

Use this:

SELECT  *
FROM    xbtit_files
WHERE   soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT  *
FROM    xbtit_files
WHERE   soha_id = ''
        AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT  *
FROM    xbtit_files
WHERE   soha_id IS NULL
        AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'

Each query uses its own index.

You can just combine it into a single query:

SELECT  *
FROM    xbtit_files
WHERE   (
        soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
        OR
        (soha_id = '' AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
        OR
        (soha_id IS NULL AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
        )

and create a composit index on (soha_id, info_hash) for this to work fast.

MySQL is also able to merge results from two indexes together, using index_merge, so there is a chance you would see this in the plan for the second query even if you don't create a composite index.