How to implement ternary conditional operator in MySQL

In MySQL, there is no direct ternary conditional operator (e.g., as in C language: condition ? true_value : false_value). However, MySQL provides the IF() function and CASE statement, which can replicate the functionality of the ternary operator.

Using the IF() Function

The IF() function is a conditional function in MySQL, with the following syntax:

shell
IF(expression, value_if_true, value_if_false)

This syntax closely mirrors the logic of the ternary operator. Specifically, expression represents the condition, value_if_true is the value returned when the condition is true, and value_if_false is the value returned when the condition is false.

Example:

Suppose we have an employees table that includes employee_id, name, and salary columns. We want to query the salary status for all employees, showing 'High' if the salary exceeds 5000, otherwise 'Low'. The SQL query is:

sql
SELECT name, IF(salary > 5000, 'High', 'Low') AS SalaryStatus
FROM employees;

Using the CASE Statement

The CASE statement is more flexible and can handle multiple conditions, similar to the switch statement in other programming languages. Its basic syntax is:

shell
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Example:

Using the same employees table, we can achieve similar functionality with the CASE statement:

sql
SELECT name, 
       CASE 
           WHEN salary > 5000 THEN 'High'
           ELSE 'Low'
       END AS SalaryStatus
FROM employees;

Both methods can achieve similar functionality to the ternary operator. The IF() function is more concise and suitable for simple conditional checks, while the CASE statement is more powerful and flexible, ideal for handling complex multi-condition scenarios.