Geuis Geuis - 2 years ago 170
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 |

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download