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

• Related Questions & Answers
• MySQL query to sum rows having repeated corresponding Id
• MySQL query to return only the rows which are numeric?
• MySQL query to order rows with value greater than zero?
• Query Array for 'true' value at index n in MongoDB?
• Count rows having three or more rows with a certain value in a MySQL table
• MySQL query to decrease value by 10 for a specific value?
• MySQL query to first set negative value in descending order and then positive value in ascending order
• MySQL query to select too many rows?
• MySQL query to select multiple rows effectively?
• MySQL query to select a random row value (Id and Name) having multiple occurrences (Name)?
• MySQL query to increase item value price for multiple items in a single query?
• MySQL query to select top n rows efficiently?
• MySQL query to count rows in multiple tables
• MySQL query to check if multiple rows exist?
• MySQL query to exclude values having specific last 3 digits