MySQL の複数テーブルの結合クエリの手順

1. マルチテーブル接続タイプ
1. MySQL のデカルト積 (クロス接続) は、CROSS JOIN または CROSS (JOIN) を省略するか、次のように ',' を使用できます。データ テーブルの積であるため、データ テーブル項目が多すぎると非常に遅くなるため、WHERE、ON、または USING 条件がある場合には使用することは一般的に推奨されません。通常は LEFT [OUTER] JOIN または RIGHT [OUTER] JOIN を使用します

2. INNER JOIN INNER JOIN は MySQL では等結合と呼ばれます。つまり、MySQL では等結合条件を CROSS と INNER JOIN に分けて指定する必要があります。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

SELECT * FROM table1 CROSS JOIN table2 
SELECT * FROM table1 JOIN table2 
SELECT * FROM table1,table2

3. MySQL の外部結合は、左外部結合と右結合に分割されます。つまり、結合条件を満たす結果を返すだけでなく、左のテーブルも結合されます。結合条件を満たさない結果 (左結合) または右テーブル (右結合) も返される必要があるため、それに応じて NULL を使用します。

例:

user table:

id | name
———
1 | libk
2 | zyfon
3 | daodao

user_action table:

user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id

result:

id | name  | action
——————————–
1 | libk     | jump      ①
1 | libk     | kick       ②
1 | libk     | jump      ③
2 | zyfon   | run        ④
3 | daodao | null       ⑤

分析:

user_action レコードに別の user_id=4、action=swim があることに注意してください。 , しかし、結果には表示されません

userテーブルのid=3、name=daodaoのユーザーはuser_actionに対応するレコードがありませんが、結果セットには表示されます
今は左結合なので、すべての作業は左が優先されます。
結果 1、2、3、4 は左のテーブルと右のテーブルの両方にあるすべてのレコードであり、5 は左のテーブルのみにあるレコードであり、右のテーブルにはありません

動作原理:

左側のテーブルから 1 つを読み取り、一致する右側のテーブル レコード (n) をすべて選択し、それらを接続して n 個のレコードを形成します (結果 1 と結果 3 などの繰り返し行を含む)。 on 条件に一致する右側のテーブル。接続されているフィールドはすべて null です。次に、次のテーブルの読み取りを続けます。

拡張:

右側のテーブルに一致するものがない場合、null が表示され、左側のテーブルにはあるが右側のテーブルには存在しないすべてのレコードが検索されるというルールを使用できます。判断は無効ではないと宣言する必要があります。

例:
sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL

(注:

1. 列の値が null の場合は、=NULL の代わりに is null を使用する必要があります

2. ここで、a.user_id 列は NOT NULL として宣言する必要があります.

)

上記の SQL の結果:

id | name | action
————————–
3 | daodao | NULL
 
——————————————————————————–

一般的な使用法:

a. 接続条件を満たす結果を返すことに加えて、同様に、接続条件を満たさない左側のテーブルのデータ列には、

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column RIGHT [OUTER] JOIN:

b に対応する NULL を使用します。

RIGHT は、結果に加えて、結合条件を満たしていないデータ列も表示する必要があることを除いて、LEFT JOIN に似ています。

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
ヒント: a.c1 = b.c1 の場合、(c1)

2 を使用することと同じです。意味的には同等です

3. MySQL がテーブルから情報を取得するときに、どのインデックスを選択するかを求めることができます。

この機能は、MySQL が可能なインデックスのリストから間違ったインデックスを使用していることを EXPLAIN が示した場合に役立ちます。

USE INDEX (key_list) を指定すると、テーブル内の行を検索するために可能なインデックスのうち最も適切なものを使用するように MySQL に指示できます。
オプションの 2 番目に選択される構文 IGNORE INDEX (key_list) を使用して、MySQL に特定のインデックスを使用しないように指示できます。例:

mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;

2. テーブル接続の制約
表示条件を追加します。 WHERE、ON、USING

1. WHERE 句

mysql>
SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2. ON

mysql>
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;

3. 2 つのテーブルが接続されている場合接続条件の列は同じ名前なので、USING

を使用できます。 例:

SELECT FROM LEFT JOIN USING ()

3 つ以上のテーブルを接続する例:

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres N cds.genreID = genres.genreID 
  
LEFT JOIN artists ON cds.artistID = artists.artistID;

または

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres ON cds.genreID = genres.genreID 
  
 LEFT JOIN artists -> ON cds.artistID = artists.artistID
  
 WHERE (genres.genre = 'Pop');

クエリを実行する場合、MySQL に関連するその他の注意事項があります。複数のテーブルがある場合は、クエリの状況に基づいてどの接続方法がより効率的かを決定する必要があります。

1. クロス結合 (デカルト積) または内部結合 [INNER | CROSS] JOIN 2. 左外部結合 LEFT [OUTER] JOIN または右外部結合 RIGHT [OUTER] JOIN の接続条件の指定に注意してください。 ON、USING .

PS: 基本的な JOIN の使用法

まず、2 つのテーブル A と B があると仮定します。それらのテーブル構造とフィールドは次のとおりです:

テーブル A:

ID Name

1 Tim

2 Jimmy

3 John

4 トム

テーブル B:

ID 趣味

1 サッカー
2 バスケットボール
2 テニス
4 サッカー

1.内部結合:

Select A.Name, B.Hobby from A, B where A.id = B.id

これは暗黙的な内部結合です。クエリ結果は次のとおりです:

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

と同じ機能があります。

Select A.Name from A INNER JOIN B ON A.id = B.id

是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。

2．  外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

 典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：

Name Hobby
Tim Football
Jimmy Basketball，Tennis
John
Tom Soccer

所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。
3．  外右联结
如果把上面查询改成外右联结：

Select A.Name from A Right JOIN B ON A.id = B.id

则结果将会是：

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

这样的结果都是我们可以从外左联结的结果中猜到的了。

说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MySQL联结查询中的某些参数的作用：
1．USING (column_list)：其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：

a LEFT JOIN b USING (c1,c2,c3)

其作用相当于下面语句

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

只是用ON来代替会书写比较麻烦而已。

2．NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有字段的Left JOIN（左联结）。

3．STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：

SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：

SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;

这并不是我们想要的效果，所以我们需要这样输入：

SELECT t1.id,t2.id,t3.id
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

MySQL联合查询效率较高，以下例子来说明联合查询（内联、左联、右联、全联）的好处：

T1表结构（用户名,密码）   
userid（int）   usernamevarchar（20）   passwordvarchar（20）   
1   jack  jackpwd   
2   owen  owenpwd   


T2表结构（用户名,密码）   
userid（int）   jifenvarchar（20）   dengjivarchar（20）   
    1   20   3   
    3   50   6   


第一：内联（inner join）
如果想把用户信息、积分、等级都列出来，那么一般会这样写：

select * from T1, T3 where T1.userid = T3.userid
（其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid ）。

把两个表中都存在userid的行拼成一行（即内联），但后者的效率会比前者高很多，建议用后者（内联）的写法。

SQL语句：
select * from T1 inner join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   

第二：左联（left outer join）
显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中；
右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：
select * from T1 left outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL   

第三：右联（right outer join）。

右側のテーブル T2 のすべての行を表示し、左側のテーブル T1 の一致条件を右側のテーブル T2 に追加します。
左側のテーブル T1 の条件が満たされない場合は、結果に追加する必要はありません。 table であり、NULL が表現されます。

SQL ステートメント:
select * from T1 right external join T2 on T1.userid = T2.userid

実行結果
T1.userid ユーザー名 パスワード T2.userid jifen dengji
1 jack jackpwd 1 20 3
NULL NULL NULL 3 50 6

4 番目: 完全外部結合

左のテーブル T1 と右のテーブル T2 のすべての行を表示します。つまり、左の結合結果テーブル + 右の結合結果テーブルを結合し、重複を除外します。

SQL ステートメント:
select * from T1 full external join T2 on T1.userid = T2.userid

実行結果
T1.userid ユーザー名 パスワード T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL NULL NULL
NULL NULL NULL 3 50 6

まとめると、結合クエリに関しては、4 つの結合メソッドを柔軟に使用できれば、基本的に複雑なステートメント構造が簡素化されます。