I have a 2 statements with limits and union, but when I run the statement I only get the number of results that defined to the highest limit. I thought I would get X results from statement 1 plus Y results from statement 2. This did not occur. Why is that?
Here is the query example:
SELECT * FROM tableA WHERE X > 100 LIMIT 2
UNION
SELECT * FROM tableA WHERE X < 100 LIMIT 3;
The query above returns 3 results. However in this simple example I wanted 2 results + 3 results (5 results altogether).
If I modified the query like so I got the 5 results, but this query below appears misleading:
SELECT * FROM tableA WHERE X > 100 LIMIT 2
UNION
SELECT * FROM tableA WHERE X < 100 LIMIT 5;
I found that by wrapping each statement in another statement resolved this. Here is how the final statement appears:
SELECT * FROM(
SELECT * FROM tableA WHERE X > 100 LIMIT 2
)query1
UNION
SELECT * FROM(
SELECT * FROM tableA WHERE X < 100 LIMIT 3
)query2;
Thanks to anyone that can explain why behaviour of the initial two statements.
Unless parenthesis are used, the final ORDER BY and/or LIMIT clauses are applied to the query as a whole "outside/after" the union.