Ric Ric - 2 months ago 7
MySQL Question

MySQL if query returns nothing, try second query

I'm crossing everything I have that this is possible...

I currently have some SQL that uses UNION ALL to join a bunch of queries together. It's important that ALL is in there, because these queries could potentially return the same records and I need each instance.

Unfortunately, the individual SELECT's aren't very well thought out. They incorporate a WHERE clause that returns all records where a value matches a user-input value, or is NULL, and I now need these to either return only the records that match, or fall back on to returning the records that are NULL only if nothing is returned by the first attempt...

In other words, I have something like this...

SELECT foo,bar
FROM foobar
WHERE foo = bar
OR foo IS NULL
UNION ALL
SELECT foo,bar
FROM foobar
WHERE foo = bar
OR foo IS NULL


and I instead need something like this...

(
TRY..
SELECT foo,bar
FROM foobar
WHERE foo = bar
..nothing? TRY..
SELECT foo,bar
FROM foobar
WHERE foo IS NULL
)
UNION ALL
(
TRY..
SELECT foo,bar
FROM foobar
WHERE foo = bar
..nothing? TRY..
SELECT foo,bar
FROM foobar
WHERE foo IS NULL
)


Is this possible? If so, what would a working example be please?

Answer

For each of your querygroups, you can use a construct like

...
UNION ALL
  SELECT foo,bar
  FROM foobar
  WHERE foo = bar
     or foo is null and not exists (
       SELECT *
       FROM foobar
       WHERE foo = bar
    )
UNION ALL
...

This will take either the rows that fulfill your condition, or, if there are no rows that fulfill your condition, are null. It can make use of an index on foo. It will be a little slower than your current query (because it has to evaluate the query for foo = bar twice).