MySQL Union Query w/ Left Join - Order By Error ?

Question

We have a Union Query. Here's a basic (similar) example:

SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "a%" UNION SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "b%" ORDER BY a.Name, b.Info;

I am receiving an error that says "Unknown column 'b.Info' in 'order clause'".

When I remove the "b.Info" from the end of the ORDER BY clause, it works.

Ideas ?

Answer 1

I think when you're doing UNION query you should specify column position, not name in the ORDER BY clause. Try ORDER BY 1,2.

Answer 2

The Orderby is being applied to the combined result-set - after the Union has taken place. At this point, there is only one table so to speak, so the reference to b.Info will be invalid.

SELECT a.Name AS 'NameCol', b.Info AS 'InfoCol' FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE 
a.Name LIKE "a%" UNION SELECT a.Name AS 'Name', b.Info AS 'Info' FROM a LEFT JOIN b ON
(a.ID = b.ID) WHERE a.Name LIKE "b%" ORDER BY NameCol, InfoCol;

Be aware that this could be potentially very slow (with large result sets), as you are forcing MySQL to use a temporary table for the sorting operation.

Answer 3

This problem is described in the MySQL documentation (12.2.8.3 UNION Syntax). You can't use the original table name, so give each column an alias and use this one in the ORDER BY clause:

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;

This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY.