Inline IF and CASE statements in MySQL

Posted: 13th August 2009 by Tim in MySQL
Tags: , , , , , ,

There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements.

The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. For example:

SELECT IF(score > 100, 100, score) AS score
FROM exam_results

this will return the value in the score column limited to a maximum value of 100. IF statements can also be nested:

SELECT IF(score > 100, 100, IF(score < 0, 0, score)) FROM exam_results

CASE statements (switch statements for those C programmers) are much like if statements. For example:

SELECT CASE num_heads
WHEN 0 THEN 'Zombie'
WHEN 1 THEN 'Human'
ELSE 'Alien'
END AS race
FROM user

This code checks the value in the num_heads column and deduces race from the values presented. CASE statements may also be nested in the same way as IF statements.

  1. James says:

    Thanks Tim, this was just what I was looking for!

  2. asdf says:

    that’s awesome

  3. PaNic says:

    Great Help! Thank you. !

  4. Sue says:

    Thanks, this really help!

  5. great knowledge….
    tx tx tx very much 4 ur shared

  6. Steven says:

    Loving it! Great stuff!

  7. Juan Pablo says:

    Hi Tim, I would ask you if is is optimus in order to made 5 to 10 if-else nested in the columns, I know that I’ve bring the results when I do made the joins and where filter and after I will made the validation. But, can this filsters onsume a lot of CPU?

    Thank Tim, nice Blog!

  8. Thedath Oudarya says:

    Thanks bro.. It’s simple and clear.. 🙂

  9. Vijay Gona says:

    Cool stuff 🙂

  10. Hmmm very nice example, simple and clear…

  11. prabhagaran says:

    Thanks.Nice example..