mysql - 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

Something like this should work in MySQL:

SELECT a.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) a
 UNION ALL 
SELECT b.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) b

Note however, absent an ORDER BY (or GROUP BY) clause on the outermost query, the order that the rows are returned is NOT guaranteed.

If you need the rows returned in a particular sequence, you should include an ORDER BY on the outermost query. In a lot of use cases, we can just use an ORDER BY on the outermost query to satisfy the results.

However, if you have a use case where you need all the rows from the first query returned before all the rows from the second query, one option is to include an extra discriminator column in each of the queries. For example, add ,'a' AS src in the first query, ,'b' AS src to the second query.

Then the outermost query could include ORDER BY src, name, to guarantee the sequence of the results.

---

FOLLOWUP

In your original query, the ORDER BY in your queries is discarded by the optimizer; since there is no ORDER BY applied to the outer query, MySQL is free to return the rows in whatever order it wants.

The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL.

Test case:

populate tables

CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;

INSERT INTO foo2 (id, role) VALUES 
  (1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf');
INSERT INTO foo3 (id, role) VALUES 
  (1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas');

query

SELECT a.*
  FROM ( SELECT s.id, s.role
           FROM foo2 s
          ORDER BY s.role
       ) a
 UNION ALL
SELECT b.*
  FROM ( SELECT t.id, t.role
           FROM foo3 t
          ORDER BY t.role
       ) b

resultset returned

    id  role     
 ------  ---------
      3  aragorn  
      2  frodo    
      5  gandalf  
      4  pippin   
      1  sam      
      2  boromir  
      3  elron    
      1  gimli    
      5  legolas  
      4  merry    

The rows from foo2 are returned "in order", followed by the rows from foo3, again, "in order".

Note (again) that this behavior is NOT guaranteed. (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). This behavior may be different in versions after 5.5.)

If you need the rows returned in a particular order, then specify an ORDER BY clause for the outermost query. And that ordering will apply to the entire resultset.

As I mentioned earlier, if I needed the rows from the first query first, followed by the second query, I would include a "discriminator" column in each query, and then include the "discriminator" column in the ORDER BY clause. I would also do away with the inline views, and do something like this:

SELECT s.id, s.role, 's' AS src
  FROM foo2 s
 UNION ALL
SELECT t.id, t.role, 't' AS src
  FROM foo3 t
 ORDER BY src, role