How to Limit Results Per Table When Using MySQL's UNION Operator?

Combined use of UNION and LIMIT operators in MySQL

When querying data from multiple tables using the UNION operator, you may need to apply the LIMIT operator to each result set separately. This is accomplished by placing LIMIT within parentheses surrounding each SELECT statement.

The following example is designed to extract 20 jobs from two specific companies, with a limit of 10 jobs per company:

Incorrect example:

SELECT c.name, j.title, j.`desc`, j.link
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company1')
UNION
SELECT c.name, j.title, j.`desc`, j.link
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company2')
ORDER BY name, title
LIMIT 20;

The LIMIT 20 of the above code acts on the result set after UNION, not a single SELECT statement.

Correct example:

(SELECT c.name, j.title, j.`desc`, j.link
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name = 'Company1'
ORDER BY name, title
LIMIT 10)
UNION
(SELECT c.name, j.title, j.`desc`, j.link
  FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name = 'Company2'
ORDER BY name, title
LIMIT 10);

By surrounding each SELECT statement with parentheses and applying LIMIT 10 individually, we effectively limit the results to the top 10 positions at each company. If any company has fewer than 10 positions, the query will still return all available results.

This approach follows the documentation's guidance that "To apply ORDER BY or LIMIT to a single SELECT, place the clause within parentheses surrounding the SELECT". It should be noted that this method only sorts and restricts each subquery, and the sorting of the final results requires additional processing. If you need to sort the final merged results, you need to add another ORDER BY statement at the outermost level.

The above is the detailed content of How to Limit Results Per Table When Using MySQL's UNION Operator?. For more information, please follow other related articles on the PHP Chinese website!