User Comments

It's not documented above, but you can use ORDER BY on a UNION that consists of only one SELECT (and thus doesn't actually include the word "UNION"). Suppose you want the last 5 entries in a table, but you want them in ascending order. You can use this query:

( SELECT * FROM table_name ORDER BY ranking DESC
LIMIT 5 ) ORDER BY ranking;

Similarly, you could select the top 10 records from a table ordered by one column and then sort them alphabetically by another column.

An alternative, rather simpler (especially with very complex select statements) way to 'use union' in 3.x might be the following:

Build a nice union query. (save it somewhere, so you can use that if you upgrade)
If you would say that query was '(*cool_select_statement_1*) UNION (*cool_select_statement_2*) *order_and_group_by_stuff*'.
You could make an replacement set of query's like this:
CREATE TEMPORARY TABLE temp_union TYPE=HEAP *cool_select_statement_1*;
INSERT INTO temp_union *cool_select_statement_2*;
SELECT * FROM temp_union *order_and_group_by_stuff*;
DROP TABLE temp_union;

Note that I've use a HEAP and TEMPORARY table because that combination is rather fast and, well, temporary.
You can't execute these query's on one line (well I coudn't), so it would look like this in PHP:
mysql_query('CREATE..', $connection);
mysql_query('INSERT..', $connection);
$query = mysql_query('SELECT..', $connection);
mysql_query('DROP..', $connection);

In addition to the above comment regarding the ORDERing of individual SELECTS, I was after a way to do exactly what is says wouldn't work. I have two playlists, and to get the correct order I need to use two different ORDER clauses, also I wanted to use the DISTINCT functionality of the UNION syntax.

What I needed was the contents of each playlist to be ordered in there specific way, while the first appeared wholly before the second. Also, I couldn't use the various tricks of adding extra colums to sort on because that left me with non-unique rows and therefore if the same entry was in both lists, the duplicate didn't get removed.

How I overcame this was to use subqueries, as follows:

SELECT song_id FROM
(SELECT song_id FROM play_immediate
ORDER BY play_id DESC) AS t1

UNION

SELECT song_id FROM
(SELECT song_id FROM play_later
ORDER BY play_id) AS t2

And using this I am able to sort each list differently, one ascending and one descending, keep the 'immediate' list before the 'later' list but still remove all duplicates.

Hope this helps others.

If you want to run a WHERE statement on the result of a UNION, you can make the union into a subquery like such:

SELECT * FROM ((
SELECT * FROM table1
WHERE ...
ORDER BY ...
LIMIT ...
) UNION (
SELECT * FROM table2
WHERE ...
ORDER BY ...
LIMIT ...
)) as t
WHERE ...
ORDER BY ...

I had a problem with a nested JOIN-Statement... nested in a UNION... Worked fine with mySql 5.0.20, but with 4.0.27 it just wouldn't... Found a workaround using IN():

Goal: For a given Article-ID and a table 'tblseealso' with an ID-List of articles: get sorted list of all referenced (to and by) articles.

Statement for 5.0.20:

SELECT DISTINCT `tblarts`.* FROM
(
(
SELECT `tblseealso`.`ID2` AS `ID` FROM `tblseealso`
WHERE `tblseealso`.`ID1` = 1234
)
UNION
(
SELECT `tblseealso`.`ID1` AS `ID` FROM `tblseealso`
WHERE `tblseealso`.`ID2` = 1234
)
) AS `Liste`
INNER JOIN `tblarts` ON `Liste`.`ID` = `tblarts`.`ID`
ORDER BY `tblarts`.`Titel`

The alternative for 4.0.27:

SELECT * FROM
(
SELECT `tblarts`.* FROM `tblarts`
WHERE
(
`tblarts`.`ID` IN
(
SELECT `tblseealso`.`ID2` FROM `tblseealso`
WHERE `tblseealso`.`ID1` = 1234
)
)
OR
(
`tblarts`.`ID` IN
(
SELECT `tblseealso`.`ID1` FROM `tblseealso`
WHERE `tblseealso`.`ID2` = 1234
)
)
) AS `Liste`
ORDER BY `Liste`.`Titel`

Note: Without nesting in the outer SELECT-Statement the ORDER BY-clause causes a "memory overload"?!

Hope this maybe helps someone... if only because someone adds a comment with a better solution ;o)

"As of MySQL 4.1.1, if the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements."

Here's an example work around for the issue described above and MySQL 4.0.x.

You can cause MySQL to reserve enough space for your columns like by starting with a placeholder statement:

(
SELECT
id, '1234567890123456789012345678901234567890' as matched_by
FROM
customer
WHERE
1=0
)
UNION ALL
(
SELECT
id, 'pet' as matched_by
FROM
customer
WHERE
pet = 'rock'
)
UNION ALL
(
SELECT
id, 'first_name' as matched_by
FROM
customer
WHERE
first_name = 'Peter'
)
UNION ALL
(
SELECT
id, 'last_name' as matched_by
FROM
customer
WHERE
last_name = 'Phillips'
)

For anyone who is wondering why this issue is important, consider that ORs tend to be very slow in MySQL 4.x however UNIONs tend to be fast. Frequently WHERE clauses that would be written with ORs are turned into separate SQL statements joined with UNION and/or UNION ALL.

For an app I was writing I needed to list both parent & child for a 2 level tree - the first half of the union returns all the parents , the second all the children for these parents - as I was using the pear html_pager I also added a limit clause....

select parent.categoryid, parent.categoryname, parent.sequence as seq, parent.categoryid as id, '' as name, 0 as seq1
from category as parent
where parent.isasubcategoryof = 0
union
select parent.categoryid, parent.categoryname, parent.sequence as seq, child.categoryid as id, child.categoryname as name, child.sequence as seq1
from category as parent, category as child
where (child.isasubcategoryof = parent.categoryid)
order by seq, seq1

In response to Phil McCarley's excellent post from February 28 2006, can I give another example where this might help?

I wanted to get the results from a table where an inner join brought about a resultset, but also wanted to display other possible values that weren't in the resultset so that they could be added. It was implemented as a single list with the existing resultset ticked. I also didn't want duplicates, and wanted the ticked items to appear at the top. This was solved using Phil's sub-query suggestion as follows:

SELECT * FROM (
(SELECT 1 AS ticked, col1, col2 FROM table1 INNER JOIN table2 USING (col3))
UNION
(SELECT 0 AS ticked, col1, col2 FROM table1)
) AS combined_table /*derived tables need a unique name*/
GROUP BY col1 /*group by the unique col1 to stop duplicates*/
ORDER BY ticked DESC

I noticed individual ordering is not working.

Example: (SELECT * FROM `table1` WHERE `active` = '1' ORDER BY `price` ASC) UNION (SELECT * FROM `table1` WHERE `active` = '0' ORDER BY `price` ASC)

When adding a individual LIMIT to each query, it works.

Example: (SELECT * FROM `table1` WHERE `active` = '1' ORDER BY `price` ASC LIMIT 30) UNION (SELECT * FROM `table1` WHERE `active` = '0' ORDER BY `price` ASC LIMIT 30)

Wilco Kuipers:
That... is exactly what it says up in the article.

UNIONs explicitly return an unordered list unless you sort the UNION as a whole. Thus, any orderings in the source SELECTs are optimized away unless they are accompanied by a LIMIT (because then they still actually do something).

WARNING: Although the manual above encourages the use of sorting columns if you want the UNION'd result sets to be displayed sequentially, using sort columns will effectively prevent the default UNION behavior of filtering out duplicate rows (i.e. UNION DISTINCT), because your sort column value will be different in the different result sets.

There are various ways of getting around this, depending on what you are sorting and why. Just write a different kind of ORDER BY clause and bear this in mind.

I was looking for iterative union but didn't really find any examples... But I found a stored proc which used a loop so I could create my own with the help from it. I'm very thankful to that guy.

My mission: Get max 3 articles of each category sorted by releasedate all done with mysql.

The i_limit-inparameter will be 3 for me, but I want it dynamic incase the customer decides they want a different amount of articles.
I'll explain a wee bit of what my code does. It loops all unique categories, because I group by categories, I sort by releasedate here because I want latest category to come first in the loop. Then in the loop I create the execute string. It adds the union on all iterations after the first.
The 02000 is the error code when a row doesn't exist, hench it has looped through it all.

CODE:
DELIMITER $$

DROP PROCEDURE IF EXISTS `dbname`.`FetchArticles`$$

CREATE DEFINER=`user`@`%` PROCEDURE `dbname`.`FetchArticles`(IN i_limit VARCHAR(5))
BEGIN
DECLARE curCategory varchar(256);
DECLARE HasData int;
DECLARE firstIteration int;
DECLARE Result cursor for SELECT ar_category FROM cms_articles GROUP BY ar_category ORDER BY ar_creationdate DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET HasData = 0;
Set @i_limit=i_limit;
OPEN Result;

SET firstIteration = 0;
SET HasData = 1;
Set @q = "";
FETCH Result INTO curCategory;
WHILE HasData = 1 DO
IF firstIteration = 0 THEN
SET firstIteration = 1;
ELSE
Set @q=concat(@q, " UNION ");
END IF;
Set @q=concat(@q, "(SELECT * FROM cms_articles WHERE ar_category = '", curCategory, "' ORDER BY ar_creationdate DESC LIMIT ", @i_limit, ")");
FETCH Result INTO curCategory;
END WHILE;
CLOSE Result;

PREPARE sel FROM @q;
EXECUTE sel;
END

Multiple UNIONs -

Didn't see any examples of this, but just wanted to note that multiple UNIONs in the same statement works just fine.

For example:
(select distinct cool_people from table_1)
UNION
(select distinct cool_people from table_2)
UNION
(select distinct cool_people from table_3);

This will return all unique cool_people from the set of distinct cool_people from each table. This can be quite handy!

A temporary table is always used for UNION and UNION ALL queries to buffer the rows from each branch of the UNION before any rows are returned to the client.

The response by Phil McCarley on 2/28/06, though old, worked like a charm. It was a bit long having so many SELECT statements, since I had to list the numerous columns I wanted to display in my output table (created with a
while($row = mysql_fetch_row($result))
command in php). There was probably a more elegant way to do what I needed to do (sort by name with all the customers having a current balance listed above all the customers without a current balance). But since this solution worked, I'm not about to mess with it now.

Add your own comment.