PDF (US Ltr)
- 27.1Mb
PDF (A4)
- 27.2Mb
HTML Download (TGZ)
- 7.2Mb
HTML Download (Zip)
- 7.2Mb
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
UNION は、複数の SELECT ステートメントからの結果を 1 つの結果セットに結合するために使用されます。
最初の SELECT ステートメントからのカラム名が、返される結果のカラム名として使用されます。各 SELECT ステートメントの対応する位置にリストされている選択されるカラムは、データ型が同じになるようにしてください。(たとえば、最初のステートメントによって選択される最初のカラムが、ほかのステートメントによって選択される最初のカラムと型が同じになるようにしてください。)
対応する SELECT カラムのデータ型が一致しない場合、UNION の結果内のカラムの型と長さは、すべての SELECT ステートメントによって取得された値を考慮に入れて決定されます。たとえば、次の例を考えてみます。
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
これらの SELECT ステートメントは通常の選択ステートメントですが、次の制限があります。
INTO OUTFILEを使用できるのは、最後のSELECTステートメントだけです。(ただし、UNIONの結果全体がファイルに書き込まれます。)HIGH_PRIORITYを、UNIONの一部であるSELECTステートメントで使用することはできません。それを最初のSELECTに対して指定しても、何の効果もありません。それを以降のいずれかのSELECTステートメントに対して指定すると、構文エラーが発生します。
UNION のデフォルトの動作では、重複した行が結果から削除されます。オプションの DISTINCT キーワードは、これも重複した行の削除を指定するため、デフォルト以外の効果は何もありません。オプションの ALL キーワードを指定すると、重複した行の削除は実行されず、その結果には、すべての SELECT ステートメントからの一致するすべての行が含まれます。
UNION ALL と UNION DISTINCT を同じクエリー内で混在させることができます。混在した UNION 型は、DISTINCT 和集合がその左側にある ALL 和集合をすべてオーバーライドするように処理されます。DISTINCT 和集合は、UNION DISTINCT を使用して明示的に、あるいはそのあとに DISTINCT または ALL キーワードのない UNION を使用して暗黙的に生成できます。
個々の SELECT に ORDER BY または LIMIT を適用するには、この句を 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);
ただし、個々の SELECT ステートメントに対して ORDER BY を使用しても、UNION がデフォルトでは、順序付けされていない行のセットを生成するため、最終的な結果に行が現れる順序には何も影響を与えません。そのため、このコンテキストでは通常、ORDER BY は LIMIT と組み合わせて使用されます。それにより、選択された行の UNION の最終結果での順序に必ずしも影響を与えるわけではないにもかかわらず、SELECT で取得するためのこれらの行のサブセットを決定するために使用されるようになります。ORDER BY が SELECT 内に LIMIT なしで現れた場合、この句はいずれにしても何も効果がないため、最適化によって削除されます。
ORDER BY または LIMIT 句を使用して UNION の結果全体をソートまたは制限するには、個々の SELECT ステートメントを括弧で囲み、最後のステートメントのあとに ORDER BY または LIMIT を配置します。次の例では、この両方の句を使用しています。
(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;括弧のないステートメントは、今示した括弧で囲まれたステートメントと同等です。
この種の ORDER BY は、テーブル名 (つまり、tbl_name.col_name という形式の名前) を含むカラム参照を使用できません。代わりに、最初の SELECT ステートメント内にカラムのエイリアスを指定し、そのエイリアスを ORDER BY 内で参照します。(あるいは、ORDER BY 内でカラムを、そのカラム位置を使用して参照します。ただし、カラム位置の使用は非推奨です。)
また、ソートされるカラムにエイリアスが指定されている場合、ORDER BY 句はそのカラム名ではなく、エイリアスを参照する必要があります。次のうちの最初のステートメントは機能しますが、2 番目は「カラム 'a' は 'order clause' にはありません」というエラーで失敗します。
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
UNION の結果内の行が、各 SELECT によって 1 つずつ取得された行のセットで構成されるようにするには、ソートカラムとして使用する各 SELECT 内の追加のカラムを選択し、最後の SELECT のあとに ORDER BY を追加します。
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
さらに個々の SELECT の結果内のソート順序を維持するには、ORDER BY 句にセカンダリカラムを追加します。
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
また、追加のカラムを使用すると、各行がどの SELECT から取得されるかを決定することもできます。追加のカラムでは、テーブル名を示す文字列などのほかの識別情報も指定できます。
( 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.
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);
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.
SELECT * FROM ((
SELECT * FROM table1
WHERE ...
ORDER BY ...
LIMIT ...
) UNION (
SELECT * FROM table2
WHERE ...
ORDER BY ...
LIMIT ...
)) as t
WHERE ...
ORDER BY ...
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)
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.
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
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
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)
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).
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.
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
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!
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.
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.
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
);
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);
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.
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:
See http://www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/
SELECT * FROM tbl1 WHERE user_id (1,2) UNION SELECT * FROM tbl2 WHERE user_id > 1000 LIMIT 0,50 => this will extract 2 plus 48 records.
As another thest, I run the same but with LIMIT 1,50 => this extracted 1 plus 49 records.