Skip navigation links

User Comments

Posted by Keith Ivey on May 8 2003 9:10am[Delete] [Edit]

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.

Posted by [name withheld] on September 1 2003 6:57pm[Delete] [Edit]

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

Posted by Phil McCarley on February 28 2006 6:37am[Delete] [Edit]

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.

Posted by James McGuigan on June 14 2006 9:39am[Delete] [Edit]

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

Posted by [name withheld] on June 24 2006 8:41pm[Delete] [Edit]

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)

Posted by Peter Phillips on July 26 2006 5:48pm[Delete] [Edit]

"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.

Posted by Kim Mihaly on October 4 2006 5:35am[Delete] [Edit]

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

Posted by Steve Owen on January 15 2008 5:37pm[Delete] [Edit]

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

Posted by Wilco Kuipers on March 5 2008 4:13pm[Delete] [Edit]

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)

Posted by Xty Y on March 26 2008 6:42pm[Delete] [Edit]

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

Posted by Stephen Dewey on May 20 2008 3:55pm[Delete] [Edit]

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.

Posted by Nicklas Holmgren on November 13 2008 4:04pm[Delete] [Edit]

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

Posted by Samuel Huckins on March 4 2009 9:17pm[Delete] [Edit]

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!

Posted by Mark Callaghan on July 31 2009 5:54pm[Delete] [Edit]

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.

Posted by Thomas Chang on February 8 2010 7:42am[Delete] [Edit]

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.

Posted by Sait Karalar on September 30 2010 9:45am[Delete] [Edit]

To select all records in different tables with table name
select *, 'table1' as tblName from table1 UNION ALL SELECT *, 'table2' as tblName FROM table2;

If your tables has different fields than

select field_1, field_2, 'table1' as tblName from table1 UNION SELECT field_3, field_4, 'table2' as tblName FROM table2;

These selects and unions "ALL" records in two different table and ads a tblName FIELD as table name at the end such that you may know the records comes from which table.

If you want unique records, do not use tblName, since it differs each row from other table and you never get unique record. No need to use UNION ALL in this case.

Note that, tblName and any other fix data should be added to end in select.

Posted by Lalit P on December 20 2011 5:40pm[Delete] [Edit]

There is an issue was in the way that MySQL parses executes the queries with UNION. When you do a UNION of two tables in MySQL, the MySQL engine just appends the second data-set to the first data set without matching the column names or warning if the data types of corresponding columns are not same.
Although the official documentation does talk about being data-type agnostic, it doesn’t mention about column name being in same order in all the queries.

Example:
Lets take this example of a table.
CREATE TABLE users (
id SMALLINT NULL,
first_name VARCHAR(50) NULL,
last_name VARCHAR(50) NULL,
points SMALLINT NULL,
level SMALLINT NULL
);

+------+------------+-----------+--------+-------+
| id | first_name | last_name | points | level |
+------+------------+-----------+--------+-------+
| 1 | Lalit | Patel | 5 | 50 |
| 2 | Chuck | Norris | 10 | 100 |
+------+------------+-----------+--------+-------+
2 rows in set (0.00 sec)

The following query runs without any error and it gives the result set given below. Note that it takes the column names in the order they were specified in the first query, but mixes the values up.

(SELECT id, first_name, last_name, level, points FROM users WHERE id = 1)
UNION
(SELECT id, last_name, first_name, points, level FROM users WHERE id = 2);

+------+------------+-----------+-------+--------+
| id | first_name | last_name | level | points |
+------+------------+-----------+-------+--------+
| 1 | Lalit | Patel | 50 | 5 |
| 2 | Norris | Chuck | 10 | 100 |
+------+------------+-----------+-------+--------+
2 rows in set (0.00 sec)

If you mix up the order of the column names, the resulting data you get will be incorrect or at least not what you would be expecting in your code. MySQL will return the result as long as the number of columns are same in both the queries. A bit too much forgiving.

Posted by Goran Ambrosic on October 26 2012 8:32am[Delete] [Edit]

I had one more example uf usage of an union feature.
We have to split a couple of our big database tables (>2TB) between the two servers/instances.
After the split of the tables, the structure of the tables remains the same.

First I took the data (in PHP) from one instance and I've build the result as a union (string) and put it to another instance to get finally the merged results.

Here the explanational (dummy) SQL:

SELECT
DATE_FORMAT(DATE_ADD(TIMESTAMP, INTERVAL 45 MINUTE) , '%Y-%m-%d %H:00:00') AS TIMESTAMP
, ROUND(SUM(E_Z_EVU), 3) AS E_Z_EVU
FROM (
SELECT '2012-09-01 08:00:00' AS TIMESTAMP, 0.0317 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:00:00' AS TIMESTAMP, 0.0316 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:15:00' AS TIMESTAMP, 0.0321 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:15:00' AS TIMESTAMP, 0.0319 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:30:00' AS TIMESTAMP, 0.0320 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:30:00' AS TIMESTAMP, 0.0319 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:45:00' AS TIMESTAMP, 0.0323 AS E_Z_EVU
UNION
SELECT '2012-09-01 08:40:00' AS TIMESTAMP, 0.0322 AS E_Z_EVU
) A
UNION
SELECT TIMESTAMP, E_Z_EVU
FROM archive_DB.SOME_TABLE
WHERE TIMESTAMP BETWEEN '2012-08-31 09:00:00' AND '2012-09-01 08:59:59'
GROUP BY DATE_FORMAT(DATE_ADD(TIMESTAMP, INTERVAL 45 MINUTE), '%Y-%m-%d %H:00:00')
ORDER BY TIMESTAMP

and here the example output:

+---------------------+---------+
| TIMESTAMP | E_Z_EVU |
+---------------------+---------+
| 2012-08-31 09:00:00 | 0.0044 |
| 2012-08-31 09:15:00 | 0.0034 |
| 2012-08-31 10:15:00 | 0.0039 |
| 2012-08-31 11:15:00 | 0.0181 |
| 2012-08-31 12:15:00 | 0.04 |
| 2012-08-31 13:15:00 | 0.0527 |
| 2012-08-31 14:15:00 | 0.0605 |
| 2012-08-31 15:15:00 | 0.0566 |
| 2012-08-31 16:15:00 | 0.0156 |
| 2012-08-31 17:15:00 | 0.0195 |
| 2012-08-31 18:15:00 | 0.0112 |
| 2012-08-31 19:15:00 | 0.001 |
| 2012-08-31 20:15:00 | 0 |
| 2012-08-31 21:15:00 | 0 |
| 2012-08-31 22:15:00 | 0 |
| 2012-08-31 23:15:00 | 0 |
| 2012-09-01 08:00:00 | 0.256 |
+---------------------+---------+

Posted by Ilan Hazan on November 7 2012 1:56pm[Delete] [Edit]

By using UNION ALL one can optimize the MySQL IN() Comparison Operations Which Include the Indexed Field.
See http://www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/