Combining Union and Limit Operations in MySQL Query

Combining UNION and LIMIT operations in MySQL query

Quoting the docs,

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);

How to limit the result of UNION ALL query?

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.

how to combine multiple select statement and limit for each of them

The required syntax is to wrap the individual limit queries in brackets like the following

(select id, title from arts where cat = 'lorem' order by ind asc limit 15)
union
(select id, title from arts where cat = 'ipsum' order by ind asc limit 15)
union
(select id, title from arts where cat = 'dolor' order by ind asc limit 15)

From the documentation:

To apply an ORDER BY or LIMIT clause to an individual SELECT [in a
union], parenthesize the SELECT and place the clause inside the
parentheses...

MySQL - Using UNION with LIMIT

Your query can be rewritten using aliased nested subqueries. This should work for you:

SELECT u.* FROM (
    (SELECT s1.title, s1.relavency, 'search1' as source FROM search1 AS s1
    ORDER BY s1.relavency DESC
    LIMIT 10)
        UNION 
    (SELECT s2.title, s2.relavency, 'search2' as source FROM search2 AS s2
    ORDER BY s2.relavency DESC
    LIMIT 10)
) AS u ORDER BY u.relavency DESC 
LIMIT 10

FYI: you misspelled "relevancy" but I preserved the misspelling so the query would work.

UNION works with CTE, but not without?

To include a LIMIT clause per SELECT in a UNION query, you must add parentheses. Like:

(  -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT max(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !
UNION
( -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT min(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !

Related:

• Calculate difference of multiple highest and lowest column values

That said, your query can be optimized. Use instead:

(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name) DESC NULLS LAST, first_name
LIMIT  1
)
UNION ALL  -- my guess
(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name), first_name
LIMIT  1
);

Subtle difference: this does not completely exclude rows with first_name IS NULL, but that only shows if all rows have first_name IS NULL.

About NULLS LAST:

• Sort by column ASC, but NULL values first?

UNION after ORDER BY and LIMIT

You can use parenthesis to allow the use of ORDER/LIMIT on individual queries:

(SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0, 1)
UNION   
(SELECT * FROM some tables WHERE ...)
ORDER BY 1   /* optional -- applies to the UNIONed result */
LIMIT 0, 100 /* optional -- applies to the UNIONed result */

How to query data from many tables using UNION in MySQL

When combining UNION and ORDER BY and LIMIT in a single query, it is important to recognise that ORDER BY and LIMIT will apply to the entire UNIONED result set. For this reason we can only specify ORDER BY and LIMIT after the final query.

The ORDER BY and LIMIT in a query that has been concatenated with a UNION or UNION ALL are not actually part of the last expression, they are actually after it. For this reason you cannot use table aliases in the ORDER BY, instead you can only use the column alias that is defined in the first expression, before the first UNION.

If you want to get the LAST 10 records from your query then we can simply reverse the order of the postID:

SELECT t1.postID, 
       t1.status,
       t1.`number`, 
       t2.reference, 
       t2.joint_date
FROM table1 t1 
INNER JOIN table2 t2  ON t1.postID=t2.postID
WHERE t1.active=1 
AND t1.userID=3
   
UNION

SELECT t3.postID, 
       t3.status,
       t3.`number`, 
       t4.reference, 
       t4.joint_date
FROM table3 t3
INNER JOIN table2 t4 ON t3.postID=t4.postID
WHERE t3.active=1 
AND t3.userID=3

ORDER BY postID DESC
LIMIT 0, 10;

Notice that I have deliberately injected a space between the last expression and the ORDER BY, this is to highlight visually that the ORDER BY and LIMIT in this query are part of the UNION and not part of the second query, the ORDER BY has also been removed from the first query as the overall result set will be re-ordered anyway.

Also note that we do not need (and cannot use) a table alias to reference the postID column.

In MySQL, can I SELECT with UNION but limit the rows from the second query based on the first query?

SELECT id,name,awesomeness from Cats where color = 'blue'

UNION

SELECT cat_id,null,badness from BadCats
    where cat_id in
    ( SELECT id from Cats where color = 'blue')
    and color = 'blue

---

Related Topics