CODEWITHSUNDEEP
mysqllimitunion
mcv
mcv

Reputation: 1460

why does 2 statements with limits using unions does not acquire all records?

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.

Upvotes: 1

Views: 28

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Your LIMIT in your first example is applied to the entire outer query. I've added some parentheses to make what's happening implicitly more obvious.

(SELECT * FROM tableA WHERE X > 100 LIMIT 2
 UNION
 SELECT * FROM tableA WHERE X < 100) 
LIMIT 3;

Upvotes: 0

chepner
chepner

Reputation: 531205

From https://dev.mysql.com/doc/refman/5.7/en/union.html:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15951

Unless parenthesis are used, the final ORDER BY and/or LIMIT clauses are applied to the query as a whole "outside/after" the union.

http://dev.mysql.com/doc/refman/5.7/en/union.html

Upvotes: 2

Related Questions