Geuis Geuis - 6 months ago 14
MySQL Question

How to replace 'Empty set' in a mysql query?

I have a mysql query:

SELECT zip AS z FROM zip WHERE zip = 90210;


When the row matching 90210 is found, it returns:

+-------+
| z |
+-------+
| 90210 |
+-------+


When the row is not found, an empty set is returned.

Empty set (0.01 sec)


What I am trying to figure out is how in the case of the empty set, I can get a response like this (note that 'false' is not important, it can be an integer, string, or whatever I need to define):

+-------+
| z |
+-------+
|'false'|
+-------+


I tried using SELECT EXISTS but the value is either 0/1 rather than the value/'false'.

SELECT EXISTS(SELECT zip AS z FROM zip WHERE zip = 90210);

Answer

To guarantee that a query returns one row, you can use aggregation. Here is one method:

SELECT COALESCE(MAX(zip), 'false') AS z
FROM zip
WHERE zip = '90210';

This assumes that zip is a string, so the types are compatible. If not, you might want to convert it to a string because you are clearly expected a string back.

As for your method, you would need to use a case statement, which in turn, would require another subquery to get the zip. Hence, I prefer the above method.