MySQL query to return TRUE for rows having positive value?

To return TRUE for positive values and FALSE for negative, use MySQL IF(). Let us first create a table −

mysql> create table DemoTable2038
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Value int
   -> );
Query OK, 0 rows affected (0.87 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2038(Value) values(57);
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable2038(Value) values(-100);;
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable2038(Value) values(-78);
Query OK, 1 row affected (0.42 sec)

mysql> insert into DemoTable2038(Value) values(78);
Query OK, 1 row affected (0.11 sec)

mysql> insert into DemoTable2038(Value) values(91);
Query OK, 1 row affected (0.09 sec)

mysql> insert into DemoTable2038(Value) values(-34);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2038;

This will produce the following output −

+----+-------+
| Id | Value |
+----+-------+
| 1  | 57    |
| 2  | -100  |
| 3  | -78   |
| 4  | 78    |
| 5  | 91    |
| 6  | -34   |
+----+-------+
6 rows in set (0.00 sec)

Here is the query to return true for rows that have positive value −

mysql> select *,if(Value > 0,true,false) as Result from DemoTable2038;

This will produce the following output −

+----+-------+--------+
| Id | Value | Result |
+----+-------+--------+
| 1  | 57    | 1      |
| 2  | -100  | 0      |
| 3  | -78   | 0      |
| 4  | 78    | 1      |
| 5  | 91    | 1      |
| 6  | -34   | 0      |
+----+-------+--------+
6 rows in set (0.00 sec)

AmitDiwan

Published on 07-Apr-2020 11:46:19

Previous Page Print Page