How Does MySQL\'s CASE Expression Function Like a Switch Statement?

Understanding MySQL's CASE Expression

Unlike the intuitive "if" statement, MySQL's CASE syntax mimics a "switch" statement. It provides two distinct formats to evaluate conditions and return corresponding results.

Syntax with Comparison Statements:

CASE</p>
<pre class="brush:php;toolbar:false">WHEN user_role = 'Manager' then 4
WHEN user_name = 'Tom' then 27
WHEN columnA <> columnB then 99
ELSE -1 --unknown

END

This format allows you to specify multiple comparison conditions using any valid SQL expressions. Each "WHEN" clause evaluates its condition, and if true, its corresponding statement is executed.

Syntax with a Single Value:

CASE user_role</p>
<pre class="brush:php;toolbar:false">WHEN 'Manager' then 4
WHEN 'Part Time' then 7
ELSE -1 --unknown

END

This concise form assumes that you are only examining one value and allows you to specify multiple "WHEN" conditions directly after the CASE expression. The "ELSE" clause is optional and provides a fallback value if none of the "WHEN" conditions are met.

Application to Your Example:

To generate a new field "role_order" based on the "user_role" field, you can utilize the second syntax as follows:

CASE user_role</p>
<pre class="brush:php;toolbar:false">WHEN 'Manager' then 5
WHEN 'Part Time' then 8
ELSE -1 --unknown

END

This expression assigns the value 5 to "role_order" when "user_role" is 'Manager', 8 when "user_role" is 'Part Time', and -1 when neither of these conditions are met.

The above is the detailed content of How Does MySQL\'s CASE Expression Function Like a Switch Statement?. For more information, please follow other related articles on the PHP Chinese website!