How to query data from many tables using UNION in MySQL

Question

I have 3 tables and would like to select data from table1&table2, then from table3&table2, and finally, concatenate both queries and get the last 10 elements of the result.
Each of these queries works fine. The problem occurs when I use UNION

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
ORDER BY t1.postID ASC
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 t3.postID ASC
LIMIT 0, 5;

I am just getting an error. How could I achieve this with one query ?

Answer 1

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.