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

Yes, let us first see the working of ternary operator in C or C++ language.

X=(X > 10 && ( X-Y) < 0) ?: X:(X-Y);

Here is the demo code in C language. After that we will check in MySQL. The C code is as follows −

#include <stdio.h>
int main() {
   int X;
   int Y;
   int result;
   printf("Enter the value for X:");
   scanf("%d",&X);
   printf("Enter the value for Y:");
   scanf("%d",&Y);
   result=( X > 1 && (X-Y) < 0) ? X: (X-Y);
   printf("The Result is=%d",result);
   return 0;
}

The snapshot of C code is as follows −

The following is the output −

The syntax for MySQL ternary operations is as follows −

select case when yourtableAliasName.yourColumnName1 > 1 AND
(yourtableAliasName.yourColumnName1-yourtableAliasName.yourColumnName2) < 0 THEN
0 ELSE (yourtableAliasName.yourColumnName1-yourtableAliasName.yourColumnName2)
END AS anyAliasName from yourTableName yourtableAliasName;

To understand the above syntax for Trenary operation, let us create a table. The query to create a table is as follows −

mysql> create table TernaryOperationDemo
   -> (
   -> X int,
   -> Y int
   -> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into TernaryOperationDemo values(10,5);
Query OK, 1 row affected (0.14 sec)
mysql> insert into TernaryOperationDemo values(5,15);
Query OK, 1 row affected (0.16 sec)
mysql> insert into TernaryOperationDemo values(20,15);
Query OK, 1 row affected (0.15 sec)
mysql> insert into TernaryOperationDemo values(15,25);
Query OK, 1 row affected (0.13 sec)
mysql> insert into TernaryOperationDemo values(10,-11);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from TernaryOperationDemo;

The following is the output −

+------+------+
| X    | Y    |
+------+------+
|   10 |    5 |
|    5 |   15 |
|   20 |   15 |
|   15 |   25 |
|   10 |  -11 |
+------+------+
5 rows in set (0.00 sec)

Here is the query for ternary operation −

mysql> select case when tbl.X > 1 AND (tbl.X-tbl.Y) < 0 THEN 0 ELSE (tbl.X-tbl.Y) END AS
Result from TernaryOperationDemo tbl;

The following is the output −

+--------+
| Result |
+--------+
|      5 |
|      0 |
|      5 |
|      0 |
|     21 |
+--------+
5 rows in set (0.00 sec)

Samual Sam

e

Updated on: 30-Jul-2019

42 Views

Kickstart Your Career

Get certified by completing the course

Get Started