CODEWITHSUNDEEP
mysqlsqlunion
anon
anon

Reputation:

MySQL: execute the second query only if the first query doesn't return result

I need this query:

SELECT * FROM best WHERE best = 'value' AND lang = 'x' 
   UNION 
SELECT * FROM best WHERE best = 'value' AND lang = 'custom' 
LIMIT 1

Basically I just need one record where best = 'value' and lang = 'x', if this record isn't found then I need to execute the second query with lang = 'custom'

Is MySQL smart enough to understand that considering there is the LIMIT 1, when the first query of union returns something he doens't need to execute the second query?

To have just one query I could do:

SELECT * FROM best WHERE best = 'value' AND lang IN ('x','custom') LIMIT 1

But with this query I can't say give more priority to record with lang = 'x'

Upvotes: 3

Views: 3528

Answers (3)

dani herrera
dani herrera

Reputation: 51665

The right anwser is Jan Hančič's answer (+1)

But an alternative solution for more future complex selections and nearly database brand independent may be:

SELECT * FROM (
  SELECT *, 1 as preference 
    FROM best WHERE best = 'value' AND lang = 'x' 
   UNION 
  SELECT *, 2 as preference 
    FROM best WHERE best = 'value' AND lang = 'custom' 
) T ORDER BY preference
LIMIT 1

Upvotes: 3

tobiasbayer
tobiasbayer

Reputation: 10379

You could wrap another SELECT around your UNION where you ORDER BY lang DESC and LIMIT 1.

SELECT * FROM (
  SELECT * FROM best WHERE best = 'value' AND lang = 'x' 
  UNION 
  SELECT * FROM best WHERE best = 'value' AND lang = 'custom' 
)
ORDER BY lang DESC LIMIT 1;

Upvotes: -1

Jan Hančič
Jan Hančič

Reputation: 53931

You could use ORDER BY FIELD():

SELECT
  *
FROM
  best
WHERE
  best = 'value'
  AND lang IN ( 'x', 'custom' )
ORDER BY FIELD ( lang, 'x', 'custom' )
LIMIT
  1

This will take care of your "priority" problem :)

Upvotes: 5

Related Questions