MYSQLにおけるUNION,UNION ALL,LEFT JOIN,RIGHT JOINについて

UNION and UNION ALL
mysql> select * from MYSQLIMP;
mysql> select * from MYSQLIMP2;
mysql> select id,n from MYSQLIMP union select id,n from MYSQLIMP2;

union

mysql> select id,n from MYSQLIMP union all select id,n from MYSQLIMP2;

union_all

mysql> select * from MYSQLIMP union select * from MYSQLIMP2;
mysql> select * from MYSQLIMP union all select * from MYSQLIMP2;

union_confirm

列数が違うとエラーになったが、列数を同じにしたら結合は問題無く出来ます。
union_confirm_other

LEFT JOIN
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 USING(id);

leftjoin


SELECT
c.name AS CLIENT,
p.name AS PROJECT,
p.start AS START,
p.end AS END
FROM client AS c
LEFT JOIN project AS p
USING (id)
WHERE p.id IS NULL
ORDER BY CLIENT;

left_join_check


SELECT
c.name AS CLIENT, p.name AS PROJECT,
p.start AS START, p.end AS END
FROM client AS c
LEFT JOIN project AS p
USING (id)
WHERE p.start BETWEEN '2009-01-00' AND '2009-12-31'
ORDER BY START;

project

RIGHT JOIN
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 USING(id);

right_join_on

LEFT JOIN & RIGHT JOINの結果比較

もし LEFT JOIN 内の ON か USING 部分内に右側のテーブルに一致する行がなければ、
全てのカラムが NULL に設定されている行が右側のテーブルに利用されます。
この事実は、別のテーブル内に対応する物を持たないテーブル内の行を見つける為に利用する事ができます。

RIGHT JOIN は LEFT JOIN と同じように機能します。コードがデータベース全体に移植できる状態を保つ為に、
RIGHT JOIN の代わりに LEFT JOIN を利用する事をお勧めします。

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

left_join_right_join

LEFT JOIN & RIGHT JOINで同じ結果を出す

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP2 RIGHT JOIN MYSQLIMP ON MYSQLIMP.n = MYSQLIMP2.n;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.n = MYSQLIMP2.n;

left_right_join

INNER JOIN
以下のINNER JOINは同じ結果を返します

SELECT Name, Language
FROM Country, CountryLanguage
WHERE Code = CountryCode
AND IsOfficial = 'T';


SELECT Name, Language
FROM Country INNER JOIN CountryLanguage
ON Code = CountryCode
WHERE IsOfficial = 'T';

inner_join

以下のQueryも同じ結果を返します

select Country.Name,City.Name,City.Population
from Country,City
where Country.Code = City.CountryCode
AND Country.IndepYear IS NULL;


select Country.Name,City.Name,City.Population
from Country INNER JOIN City
ON Country.Code = City.CountryCode
WHERE Country.IndepYear IS NULL;


select Country.Name,City.Name,City.Population
from Country CROSS JOIN City
ON Country.Code = City.CountryCode
WHERE Country.IndepYear IS NULL;

    Additional Sample

追加のINNER JOINのサンプルですが、INNER JOINを利用した方がすっきりしてます。
※ここでは、自己結合してます。


SELECT C2.Name AS 'Country',
C1.Name AS 'Other Countries',
C1.Region AS 'Continent',
C1.SurfaceArea
FROM Country as C1,Country as C2 WHERE
C1.SurfaceArea > (select SurfaceArea from Country where Name = 'Paraguay')
and C2.Name = 'Paraguay'
and C1.Continent = C2.Continent
and C1.Region = 'South America';


SELECT
c1.Name AS 'Country',
c2.Name AS 'Other Countries',
c2.Continent AS 'Continent',
c2.SurfaceArea AS 'Surface Area'
FROM Country AS c1
INNER JOIN Country AS c2
USING (Continent)
WHERE c2.SurfaceArea > c1.SurfaceArea
AND c1.Name = 'Paraguay';

inner_join_on_select

※ その他INNER JOINサンプル(この例だとID列は同じ名前なので、結合部分は、USING(ID)でもOK。

select client.Name,project.Name,project.START,project.END
from client inner join project on client.ID = project.ID
where project.NAME = 'Project2';

inner_join_two_tables

自己結合

SELECT c1.Name, c2.Name, c1.Population
FROM Country AS c1, Country AS c2
WHERE c1.Population = c2.Population
AND c1.Name != c2.Name AND c1.Population > 0;

self_join

上記テーブルは自己結合して、人口が同じ国を抽出してます。
当然自分と同じ国名を省いているのと、人口が0以上の国を選択してます。
上位4カ国を通常どおりselectすると以下のような結果になります。
mysql> select Name,Population from Country where Population in(68000,78000)
-> order by Population;

+————————–+————+
| Name | Population |
+————————–+————+
| American Samoa | 68000 |
| Antigua and Barbuda | 68000 |
| Andorra | 78000 |
| Northern Mariana Islands | 78000 |
+————————–+————+
4 rows in set (0.01 sec)

mysql>

参考サイト
12.2.7.1. JOIN 構文


UNION は、結果を複数 SELECT ステートメントから単一結果セットに
結合させる為に利用されます。
最初の SELECT ステートメントからのカラム名は、返された結果のカラム名
として利用されます。各 SELECT ステートメントの対応する位置にリスト
されている選択されたカラムは、同じデータ タイプを持つ必要があります。
(例えば、最初のステートメントに選択された最初のカラムは、
別のステートメントに選択された最初のカラムと同じタイプを持つ必要があります。)

————————————————
幾つかのテーブルに散らばった情報を1つの
Queryにて全て表示する為には、UNION ALLにて
テーブルを連結し情報を選択し抽出。
————————————————
SELECT
comment
FROM test.A10
UNION ALL
SELECT comment FROM test.a10
ORDER BY comment;

————————————————
幾つかのテーブルに散らばった情報を1つの
Queryにて全て表示する為には、UNION ALLにて対応。
しかし重複した情報は一つにまとめて表示する為には、
UNIONにて対応。(ALLは利用しない)
————————————————
SELECT
comment
FROM test.A10
UNION
SELECT comment FROM test.a10
ORDER BY comment;

union

12.2.7.2. UNION 構文