Combining UNION and LIMIT operations in MySQL query

Nailing the UNION-LIMIT combo

Finding the perfect balance between UNION and LIMIT can be a bit of a dance. Here are key rules to live by:

• Encapsulate your SELECT statements within parentheses when using ORDER BY or LIMIT in a UNION query.
• Keep column consistency- make sure each UNION subquery has the same number of columns with matching data types.
• To limit results per selection (like 10 jobs per company), LIMIT inside each set of parentheses then perform UNION.

Grasping LIMIT within UNION subqueries

Suppose you wish to get a fixed number of records per group (let's say, 10 jobs for each company). You can use:

(SELECT * FROM jobs WHERE company_id = 1 ORDER BY post_date DESC LIMIT 10) 
UNION ALL 
(SELECT * FROM jobs WHERE company_id = 2 ORDER BY post_date DESC LIMIT 10)


This ensures that only up to 10 jobs from each given company are returned before they are combined via UNION.

Keeping the UNION fair

If you want to maintain an equal representation from each group post UNION, a LIMIT clause within the subqueries guarantees that no individual group leans on the final dataset's scale.

Steering clear of traps

Don't be tripped by UNION ALL and UNION DISTINCT:

• UNION ALL includes all entries, duplicates in tow.
• UNION DISTINCT erases duplicates but tangles up with LIMIT inside subqueries. Stick to UNION ALL for this job.

Pro tips and tricks

Taming Pagination in UNION

For pagination in UNION queries, consider subqueries with variables keeping a tally of rows:

SELECT * FROM (
    SELECT @row_num := @row_num + 1 AS row, t.* FROM (
        (SELECT * FROM table1 LIMIT 10) -- table1 slaps!
        UNION ALL
        (SELECT * FROM table2 LIMIT 10) -- table2 is bae 😙
    ) AS t, (SELECT @row_num := 0) AS r -- @row_num wants to say "Hello, world!"
) AS numbered_results
WHERE row BETWEEN 10 AND 20; 


Comment for Reddit folks: It works! No StackOverflow submission needed!

Optimizing by creating indexes

When the situation involves large datasets, you would want to optimize your UNION queries with indexes. Check all ordering and filtering columns in the subqueries for proper indexing.

Nullifying sparse data issues

If some subqueries fetch less data than the LIMIT, the final dataset may be uneven. Use programmatically generated SQL or a server-side logic to adjust imbalances.

Accounting for dynamic SQL flexibility

For extremely dynamic datasets, consider creating a stored procedure. This creates the UNION query with dynamic SQL, hinging on input parameters such as varying company IDs or LIMIT values.