Does Ternary operation exist in MySQL just like C or C++?

Yes, ternary operation exists in MySQL using the CASE WHEN statement, which provides similar conditional logic to the ternary operator in C/C++. Let us first examine how the ternary operator works in C and then see its MySQL equivalent.

Syntax

C Ternary Operator:

condition ? value_if_true : value_if_false

MySQL Equivalent:

CASE WHEN condition THEN value_if_true ELSE value_if_false END

Example: C Ternary Operator

Here is a complete C program demonstrating the ternary operator −

#include <stdio.h>

int main() {
    int X = 5;
    int Y = 15;
    int result;
    
    printf("X = %d, Y = %d\n", X, Y);
    result = (X > 1 && (X - Y) < 0) ? X : (X - Y);
    printf("Result using ternary operator: %d\n", result);
    
    /* Test with different values */
    X = 20;
    Y = 15;
    printf("\nX = %d, Y = %d\n", X, Y);
    result = (X > 1 && (X - Y) < 0) ? X : (X - Y);
    printf("Result using ternary operator: %d\n", result);
    
    return 0;
}

X = 5, Y = 15
Result using ternary operator: 5

X = 20, Y = 15
Result using ternary operator: 5

MySQL Ternary Operation Example

MySQL uses CASE WHEN to achieve similar conditional logic. Here's how to implement the same operation −

Note: The following examples demonstrate MySQL syntax. To execute these queries, you need MySQL installed and running.

First, create a table for demonstration:

CREATE TABLE TernaryOperationDemo (
    X INT,
    Y INT
);

INSERT INTO TernaryOperationDemo VALUES 
(10, 5), (5, 15), (20, 15), (15, 25), (10, -11);

Now apply the ternary-like operation using CASE WHEN:

SELECT X, Y,
    CASE WHEN X > 1 AND (X - Y) < 0 
         THEN X 
         ELSE (X - Y) 
    END AS Result 
FROM TernaryOperationDemo;

Output Comparison

Key Points

• C ternary operator: condition ? true_value : false_value
• MySQL equivalent: CASE WHEN condition THEN true_value ELSE false_value END
• Both provide conditional logic for selecting values based on boolean conditions
• MySQL CASE WHEN is more verbose but equally powerful

Conclusion

MySQL does provide ternary-like operations through the CASE WHEN statement, offering the same conditional logic as C's ternary operator. While the syntax differs, both allow efficient conditional value assignment based on boolean expressions.