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

Taken from:
http://mysqld.active-venture.com/Miscellaneous_functions.html

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole. The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:
The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

It may be useful to know from which SELECT statement a record has been selected, for example:

SELECT t.*, 1 as n
FROM table1 t
UNION
SELECT t.*, 2 as n
FROM table2 t
UNION
SELECT t.*, 3 as n
FROM table3 t

You will find in column "n" the SELECT #

Another usage is to emulate the mixing of UNION DISTINCT and UNION ALL using this number (interesting on MySQL versions prior to 4.1.2):

SELECT t.*, 1 as n
FROM table1 t
UNION
SELECT t.*, 1 as n
FROM table2 t
UNION
SELECT t.*, 2 as n
FROM table3 t

This will select DISTINCTly records from table1 and table2 but with all records from table3 !

Patrick Allaert
http://users.pandora.be/patrick_allaert/

Pay VERY close attention to this line:

"ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away."

It will save you hours of headaches.

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)

To reiterate the guy above:

"ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away."

The additional thing to note is that you can set the LIMIT value beyond the numbers of rows you get returned to get all the results back. As long as you just have that LIMIT it will sort by individual SELECT statements.

I used this in order to a make a subquery (part of a UNION) display in random order (ORDER BY RAND()).

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

If you're sorting on the same field in both queries, but you want to keep the result sets in a specific order, try aliasing a static number to use as an ORDER BY parameter:

(SELECT region_code AS code, region_name, 0 as sort_by FROM regions)
UNION
(SELECT country_code, country_name, 1 as sort_by FROM countries)
ORDER BY code, sort_by

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

"Only the last SELECT statement can use INTO OUTFILE."

This does NOT mean that only the results of the last SELECT go into the file. All of the SELECT statements' resultsets will go into the OUTFILE.

If you ever need to have a column that tells you what table the data came from:

(SELECT *, 'Table_Name1' as location FROM Table_Name) UNION (SELECT *, 'Table_Name2' as location FROM Table_Name)

I work on a web application that has to order some data combining information from two fields (of the same table). One field of the table is for costs (of type BIGINT), and the other is an ENUM for currencies ('Euro', 'USD', 'RON'). To give good reason, 100 Euro > 100 USD (in my country).
First of all, you should convert all the currencies to only one (in this case, 1 Euro = 3.3727 RON, 1 USD = 2.5681 RON, 1 RON = 1 RON, of course). Note: If you run this query from a PHP script, you may dinamically input the exchange rates.

I've used UNIONs and ordered the result set with an ORDER BY the alias set on the first SELECT.

(
SELECT product_cost * 3.3727 AS a, product_cost_currency
FROM tbl_products
WHERE product_cost_currency = 'Euro'
ORDER BY a ASC
)
UNION (

SELECT product_cost * 2.5681 AS a, product_cost_currency
FROM tbl_products
WHERE product_cost_currency = 'USD'
ORDER BY a ASC
)
UNION (

SELECT product_cost *1 AS a, product_cost_currency
FROM tbl_products
WHERE product_cost_currency = 'RON'
ORDER BY a ASC
)
ORDER BY a ASC

However, the intermediary use of ORDER BY clauses (from the different SELECTs) is redundant, so are the last two aliases definitions.

Add your own comment.