Geek Answers Handbook

MySQL returns default if dataset is empty

I am trying to query a database using:

SELECT coalesce(value, "NA") as value 
FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
WHERE chrom = 1 AND site = 120

This query works fine if I have an entry in the database. However, if the record is not found, it returns an empty data set. I would like to modify the query so that if it is not found in the database table, it will still return some value (for "value"), and not an empty dataset

+4
source share
2 answers

how about adding UNION?

(   SELECT coalesce(value, "NA") as value 
    FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
    WHERE chrom = 1 AND site = 120
)
UNION ALL
(   SELECT 'NA' as value
    FROM mytable
    WHERE NOT EXISTS 
    (   SELECT coalesce(value, "NA") as value 
        FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
        WHERE chrom = 1 AND site = 120
    )
)

or if you want to do a joint operation once so that you can change the returned part, if null is more easily you can select it like this.

SELECT COALESCE(value, "NA") as value
FROM
(   
    (   SELECT value
        FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
        WHERE chrom = 1 AND site = 120
    )
    UNION ALL
    (   SELECT NULL as value
        FROM mytable
        WHERE NOT EXISTS 
        (   SELECT value as value 
            FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
            WHERE chrom = 1 AND site = 120
        )
    )
) t

Demo

+1
source

() : ( ), :

SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=120;

:

mysql> select * from oneline;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0,00 sec)

mysql> select * from mytable;
+-------+-------+------+
| value | chrom | site |
+-------+-------+------+
| test  |     1 |  120 |
+-------+-------+------+
1 row in set (0,00 sec)

mysql> SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=120;
+-------------------------+
| coalesce(m.value, "NA") |
+-------------------------+
| test                    |
+-------------------------+
1 row in set (0,00 sec)

mysql> SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=119;
+-------------------------+
| coalesce(m.value, "NA") |
+-------------------------+
| NA                      |
+-------------------------+
1 row in set (0,00 sec)
0

More articles:


All Articles