CODEWITHSUNDEEP
mysqlsql
Geuis
Geuis

Reputation: 42297

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);

Upvotes: 2

Views: 2154

Answers (3)

Ishamael
Ishamael

Reputation: 12795

This query will do what you want. Selecting from Dual means selecting from no table. The idea is to select all the zip codes, and union it with "false" string (you can replace it with whatever you want) if there's no entry for that zip code.

SELECT zip AS z FROM zip WHERE zip = 90210
    UNION ALL
SELECT "false" FROM dual WHERE NOT EXISTS(SELECT zip AS z FROM zip WHERE zip = 90210);

EDIT: thanks to Paul Griffin, here's a sql fiddle: http://sqlfiddle.com/#!9/9b169/8

Upvotes: 1

cdonner
cdonner

Reputation: 37708

I would typically do a UNION, something like

create table #zip (zip int);
insert into #zip values (90210);

SELECT zip AS z FROM #zip WHERE zip = 90210
UNION
SELECT 0 WHERE NOT EXISTS (SELECT 1 FROM #zip WHERE zip = 90210);

If you want to return text, you have to change the type in both queries - they have to match.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 3

Related Questions