How to use ORDER BY inside UNION

Question

I want to use ORDER BY on every UNION ALL queries, but I can't figure out the right syntax. This is what I want:

(
SELECT id, user_id, other_id, name 
FROM tablename 
WHERE user_id = 123 AND user_in IN (...) 
ORDER BY name
)
UNION ALL
(
SELECT id, user_id, other_id, name 
FROM tablename 
WHERE user_id = 456 AND user_id NOT IN (...) 
ORDER BY name
)

EDIT: Just to be clear: I need two ordered lists like this, not one:

1 2 3 1 2 3 4 5

Thank you very much!

Answer 1

Don't use ORDER BY in an individual SELECT statement inside a UNION, unless you're using LIMIT with it.

The MySQL docs on UNION explain why (emphasis mine):

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

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

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. The following example uses both clauses:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

It seems like an ORDER BY clause like the following will get you what you want:

ORDER BY user_id, name