Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all

Example query

select id from
(
 select id from table where id = 1
 union all
 select id from table where id = 2
) as t
limit 1

If the table has 1, 2 in it, will 1 always be returned for the query or can 2 appear?

Engine is currently MyIASM but I'd also appreciate an answer for InnoDB.

share|improve this question
4  
No, there is no guarantee. LIMIT without ORDER BY means that, whether it's a UNION, UNION ALL or any other subquery. –  ypercube Feb 7 at 20:35
    
If the limit statement is removed, does the union all statement then have an enforced order? As in will the first query always return first in the set –  Ryaner Feb 7 at 21:06
2  
What is the motivation for the question? Are you looking for a way to avoid executing the second query if the first returns a result? –  Martin Smith Feb 7 at 21:19
    
Pretty much. Its a high latency situation. A union like this was one of the options we came up and we didn't know if there would be an enforced order or not, thus the question. –  Ryaner Feb 7 at 22:17
    
If both parts of UNION ALL read the same table and your underlying concern is how to speed up the query, you could try the approach as in this SO answer (of mine). That may not work equally well for UNIONing sets retrieved from different tables. –  Andriy M Feb 8 at 9:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.