SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION
is used to combine the
result from multiple SELECT
statements into a single result set.
UNION
is available from MySQL
4.0.0 on.
The column names from the first
SELECT
statement are used as the
column names for the results returned. Selected columns listed
in corresponding positions of each
SELECT
statement should have the
same data type. (For example, the first column selected by the
first statement should have the same type as the first column
selected by the other statements.)
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. For example,
consider the following:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
Before MySQL 4.1.1, only the type and length from the first
SELECT
would have been used and
the second row would have been truncated to a length of 1:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| b |
+---------------+
The SELECT
statements are normal
select statements, but with the following restrictions:
Only the last SELECT
statement can use INTO OUTFILE
. (However,
the entire UNION
result is
written to the file.)
HIGH_PRIORITY
cannot be used with
SELECT
statements that are
part of a UNION
. If you
specify it for the first
SELECT
, it has no effect. If
you specify it for any subsequent
SELECT
statements, a syntax
error results.
The default behavior for UNION
is
that duplicate rows are removed from the result. The optional
DISTINCT
keyword (introduced in MySQL 4.0.17)
has no effect other than the default because it also specifies
duplicate-row removal. With the optional ALL
keyword, duplicate-row removal does not occur and the result
includes all matching rows from all the
SELECT
statements.
Before MySQL 4.1.2, you cannot mix
UNION ALL
and
UNION DISTINCT
in the same query. If you use ALL
for one
UNION
, it is used for all of
them. As of MySQL 4.1.2, mixed
UNION
types are treated such that
a DISTINCT
union overrides any
ALL
union to its left. A
DISTINCT
union can be produced explicitly by
using UNION
DISTINCT
or implicitly by using
UNION
with no following
DISTINCT
or ALL
keyword.
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
. (Alternatively, refer
to the column in the ORDER BY
using its
column position. However, use of column positions is
deprecated.)
Also, if a column to be sorted is aliased, the ORDER
BY
clause must refer to the
alias, not the column name. The first of the following
statements will work, but the second will fail with an
Unknown column 'a' in 'order clause'
error:
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b; (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
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 cause rows in a UNION
result
to consist of the sets of rows retrieved by each
SELECT
one after the other,
select an additional column in each
SELECT
to use as a sort column
and add an ORDER BY
following the last
SELECT
:
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
To additionally maintain sort order within individual
SELECT
results, add a secondary
column to the ORDER BY
clause:
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
Use of an additional column also enables you to determine which
SELECT
each row comes from. Extra
columns can provide other identifying information as well, such
as a string that indicates a table name.
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.