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

Question

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'

Jan Hančič · Accepted Answer · 2011-12-02 09:10:53Z

5

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

answered Dec 2, 2011 at 9:10

Jan Hančič

54k17 gold badges99 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jan Hančič Over a year ago

The LIMIT 1 in your first query is part of the second UNION query, so yes both will execute.

Jan Hančič Over a year ago

Nope :) You are missing ( & ) around your queries for that LIMIT to limit the whole result. Right now it just limits the second query.

dani herrera · Accepted Answer · 2011-12-02 09:19:01Z

3

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

answered Dec 2, 2011 at 9:19

dani herrera

52k11 gold badges133 silver badges188 bronze badges

2 Comments

dani herrera Over a year ago

@yes123, True. But there is an objection about this? I said that this was not the solution. Just to add information. (I can delete answer if disturbs)

fipsi Over a year ago

I think you're answer solves the problem more elegantly.. Thanks for this answer it helped me a lot ;) +1

tobiasbayer · Accepted Answer · 2011-12-02 09:11:56Z

-1

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;

answered Dec 2, 2011 at 9:11

tobiasbayer

10.4k4 gold badges48 silver badges66 bronze badges

2 Comments

Jan Hančič Over a year ago

I imagine X is just a place-holder, it could be anything so your query will not always work. And he is trying to achieve that the second query in the UNION would not run, which your query does not accomplish.

tobiasbayer Over a year ago

The decision between running or not running a query is not possible in a declarative language like SQL. For the particular example, my answer solves the priority problem. However, I admit that your solution is more elegant (so +1 for that).

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related