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
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.