How to limit the result of UNION ALL query?

Question

I have a query like this:

select col1, col2 from table1 where col1 = ?
union all
select col1, col2 from table2 where col2 = ?

Now I need to limit the result of the above query, Now I want to know, if I use limit clause after second select, then just the result of second select will be limited or the result of both select?

Anyway, which approach is good for limiting the result of union all query?

One:

select col1, col2 from table1 where col1 = ?
union all
select col1, col2 from table2 where col2 = ?
limit ?,10

Two:

select * from
    (
    select col1, col2 from table1 where col1 = ?
    union all
    select col1, col2 from table2 where col2 = ?
    ) x
limit ?,10

Giorgos Betsos · Accepted Answer · 2015-10-11T16:13:10.700

3

According to MySQL manual:

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.

Hence, you can use:

(select col1, col2 from table1 where col1 = ?)
union all
(select col1, col2 from table2 where col2 = ?)
LIMIT ?, 10

Using a sub-query should also work, but can't be more efficient in comparison to the above query.

edited Oct 11 '15 at 16:13

answered Oct 11 '15 at 16:10

Giorgos Betsos

71,379
9
63
98

Are you sure the mean of "parenthesize the individual SELECT statements" is writing each select between paranthesize? – stack Oct 11 '15 at 16:17
Yes, it's in the manual. You can check the example cited there. – Giorgos Betsos Oct 11 '15 at 16:18
@stack This is also from the manual: *A statement without parentheses is equivalent to one parenthesized as just shown.*. – Giorgos Betsos Oct 11 '15 at 16:20
@stack This means you can omit parentheses if you like. But IMO it's cleaner to use parentheses. – Giorgos Betsos Oct 11 '15 at 16:28

score 2 · Answer 2 · answered Oct 11 '15 at 16:10

2

The first is better from a performance perspective. The second materializes the subquery, which is additional overhead.

Note: You are using limit without an order by, so the results may not be consistent from one execution of the query to the next.

You should be using order by, which probably makes it irrelevant which version you use (because the order by needs to read and write the data anyway).

answered Oct 11 '15 at 16:10

Gordon Linoff

1,242,037
58
646
786

@stack . . . The important point is that if you are going to use `limit` then you should use `order by`. – Gordon Linoff Oct 11 '15 at 20:07
*"which makes it irrelevant which version you use"*. Honestly I can not understand that. can you tell me how should I use both `limit` and `order by` in my query? tnx – stack Oct 12 '15 at 14:15

How to limit the result of UNION ALL query?

2 Answers2