Inline IF and CASE statements in MySQL

10,908 0
Summary

MySQL offers inline IF and CASE statements, allowing developers to embed conditional logic directly within their queries. The IF statement takes a condition, a true value, and a false value, which can be static or column values, and supports nesting for more complex scenarios like value capping. Similarly, the CASE statement provides a switch-like structure to evaluate an expression against multiple WHEN clauses and an optional ELSE clause, also supporting nesting. These powerful constructs enable dynamic data manipulation and categorization directly within SQL, enhancing query flexibility.

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.

Source : http://timmurphy.org/2009/08/13/inline-if-and-case-statements-in-mysql/

SQL CONDITION CASE MYSQK IF

  RELATED

  COMMENTS

0

No comment for this article.