Search the MySQL manual:
Subscribe to the monthly
MySQL Newsletter!

6.4.1.2 Синтаксис оператора UNION

SELECT ...
UNION [ALL]
SELECT ...
  [UNION 
   SELECT ...]

Оператор UNION реализован в MySQL 4.0.0.

UNION используется для объединения результатов работы нескольких команд SELECT в один набор результатов.

Столбцы, перечисленные в части select_expression должны быть одинакового типа. Имена столбцов, указанные в первом SELECT будут использованы как имена столбцов для всего результата.

Эти команды SELECT являются обычными командами выборки данных, но со следующим ограничением:

Если не используется ключевое слово ALL для UNION, все возвращенные строки будут уникальными, так как по умолчанию подразумевается DISTINCT для всего результирующего набора данных. Если указать ключевое слово ALL, то результат будет содержать все найденные строки из всех примененных команд SELECT.

Если для всего результата UNION необходимо применить оператор ORDER BY, следует использовать круглые скобки: 

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

User Comments

Posted by David Ranney on February 18 2003 4:54pm[Delete] [Edit]

One little gotcha with UNION. The manual says that the
columns you select should be of the same type. That means
that if you SELECT literal values first, as you might if you wanted column headers to be printed, subsequent SELECTs
in the UNION will be limited to the lengths of strings you give.

Example:
If you run a SELECT statement such as this:
SELECT
"foo" foo_col,
"bar" bar_col,
"zama" zama_col
UNION ALL
SELECT
foo_col,
bar_col,
zama_col
FROM
tabname
...

The returned columns will be limited to 3, 3, and
4 characters in length respectively because that was
the length of the first "row" returned.

To workaround this, select an empty row from the
true table first:

SELECT
foo_col,
bar_col,
zama_col
FROM
tabname
WHERE
1=0
UNION
SELECT
"foo" foo_col,
"bar" bar_col,
"zama" zama_col
UNION ALL
SELECT
foo_col,
bar_col,
zama_col
FROM
...

Posted by Keith Ivey on May 8 2003 1:10pm[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 Michael Bailey on July 7 2003 3:01pm[Delete] [Edit]

If you are still working in MySQL 3.x and need to know how to emulate the UNION statement, here's a good article to check out:

http://jinxidoru.com/tutorials/union.html

Posted by [name withheld] on September 1 2003 10: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);

Add your own comment.