Menu
But, when I do, one of the features that makes it so powerful is the fact that you can apply both an ORDER BY clause and a LIMIT clause to the derived result set of the union (at least in MySQL). Furthermore, the individual SELECT statements can also have their own ORDER BY and LIMIT clauses.
The MySQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.
You can use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. UNION is available as of MySQL 4.0.
The MySQL LIMIT Clause The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Let's say I want to perform this query:
(SELECT a FROM t1 WHERE a=10 AND B=1) UNION ALL (SELECT a FROM t2 WHERE a=11 AND B=2) UNION ALL (SELECT a FROM t3 WHERE a=12 AND B=3) ORDER BY a LIMIT 1000; Is MySQL smart enough to skip "t3" if 550 results are available in "t1" and 450 in "t2"?
I'm looking at MySQL docs (http://dev.mysql.com/doc/refman/5.1/en/union.html) but can't seem to find the answer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With