MacLochlainns Weblog

Somebody was complaining that you couldn’t just get a Yes/No answer from a query. Yes when rows are found and No when rows aren’t found, like an “In-stock” or “Out-of-stock” message combo from a query. He didn’t like having to handle an Empty set by writing logic in PHP to provide that “Out-of-stock” message.

I told him he was wrong, you can get a a Yes/No answer from a query. You just write it differently, instead of a query like this, which get the “In-stock” message but forces you to handle the “Out-of-stock” message in the PHP code base on no records found in the query.

SELECT  'In-stock'
FROM    item
WHERE   item_title = 'Star Wars II'
LIMIT 1;

It’s simpler to write it like the one below. You gets a Yes/No answer from a query whether a row matches the query condition or not:

SELECT IF('Star Wars VII' IN (SELECT item_title FROM item)
         ,'In-stock','Out-of-stock') AS yes_no_answer;

You can also write it this more generic way, which works in Oracle and MySQL:

SELECT CASE
         WHEN 'Star Wars VII' IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM   dual;

There’s no Star Wars VII yet, but this returns the desired result when it’s not found in the data set. It also works when you find Star Wars II in the data set. Never, say never … 🙂

A more useful and complete approach with this technique is shown below with data fabrication.

SELECT inline.query_string
,      CASE
         WHEN inline.query_string IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM  (SELECT 'Star Wars II' AS query_string FROM dual
       UNION ALL
       SELECT 'Star Wars VII' AS query_string FROM dual) inline;

The query runs in an Oracle or MySQL database and returns the following result set:

+---------------+---------------+
| query_string  | yes_no_answer |
+---------------+---------------+
| Star Wars II  | In-stock      |
| Star Wars VII | Out-of-stock  |
+---------------+---------------+

Hope this helps somebody else too.