検索

ホームページ  >  記事  >  データベース  >  MysqlのJOINの詳しい説明

MysqlのJOINの詳しい説明

coldplay.xixi
coldplay.xixi転載
2020-12-04 14:45:561754ブラウズ

mysql ビデオ チュートリアル詳細なコラムの説明参加

MysqlのJOINの詳しい説明

##関連する無料学習の推奨事項:

mysql ビデオ チュートリアル

0 インデックス

  • JOINステートメントの実行順序
  • INNER/LEFT/RIGHT/FULL JOIN
  • ON と WHERE
1 の違い 概要

完全な SQL このステートメントは、句の実行中に仮想テーブル (vt) が生成されますが、結果は最後の仮想テーブルのみを返します。この考えから出発して、JOIN クエリの実行プロセスを理解し、いくつかの一般的な質問に答えようとします。

さまざまな JOIN の実行結果についてよくわからない場合は、以下のこの記事を読んでください。

2 JOIN 実行シーケンス

以下は JOIN クエリの一般的な構造です

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join condition> 
        WHERE <where_condition>
実行順序は次のとおりです

(SQL ステートメントで最初に実行されるのは常に FROM 句です) :

  • FROM:右と左 2 つのテーブルのデカルト積が実行されて、最初のテーブル vt1 が生成されます。行数は n*m です (n は左側のテーブルの行数、m は右側のテーブルの行数です)
  • ON: vt1 を行ごとにフィルタリングします。 ON 条件に変換し、結果を vt2 に挿入します。
  • JOIN: 外側の行を追加 if LEFT JOIN( LEFT OUTER JOIN## が指定されている #) 場合、最初に left table の各行を走査します。vt2 にない行は vt2 に挿入され、行の残りのフィールドはNULL で埋められ、vt3 が形成されます。RIGHT JOIN が指定されている場合も同様です。 INNER JOIN が指定されている場合、外部行は追加されません。上記の挿入プロセスは無視され、vt2=vt3 (したがって、INNER JOIN のフィルター条件が配置されます) #ON または WHERE の実行結果に違いはありません。詳細は後述します) WHERE: vt3 で条件フィルタリングを実行し、条件を満たす行を出力 vt4 に移動
  • #SELECT: vt4 ～ vt5 の指定フィールドを取り出す
  • 例を使用して、上記のテーブルを結合するプロセスを紹介しましょう (この例は良い例ではありません) 結合構文を説明するためだけに練習してください) 3 例
ユーザー情報テーブルの作成: 

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
ユーザー残高テーブルの作成: 

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

何でもデータをインポート: 

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
|   1001 | x    |
|   1002 | y    |
|   1003 | z    |
|   1004 | a    |
|   1005 | b    |
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+
8 rows in set (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)
合計 8 人のユーザーがユーザー名を持っています、4 人のユーザーがアカウント残高を持っています。

ユーザー名とユーザー ID 1003 の残高を取得します。SQL は次のとおりです

:

SELECT i.name, a.money 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON i.userid = a.userid 
        WHERE a.userid = 1003;

ステップ 1: FROM 句を実行して、 2 つのテーブルに対するデカルト積演算
デカルト積演算の後、2 つのテーブルのすべての行の組み合わせが返されます。左側のテーブル user_info には 8 行があり、右側のテーブル user_account には 4 行があり、生成された仮想テーブル vt1 は 8*4=32 行です: 

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1001 |    22 |
|   1003 | z    |   1001 |    22 |
|   1004 | a    |   1001 |    22 |
|   1005 | b    |   1001 |    22 |
|   1006 | c    |   1001 |    22 |
|   1007 | d    |   1001 |    22 |
|   1008 | e    |   1001 |    22 |
|   1001 | x    |   1002 |    30 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1002 |    30 |
|   1004 | a    |   1002 |    30 |
|   1005 | b    |   1002 |    30 |
|   1006 | c    |   1002 |    30 |
|   1007 | d    |   1002 |    30 |
|   1008 | e    |   1002 |    30 |
|   1001 | x    |   1003 |     8 |
|   1002 | y    |   1003 |     8 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   1003 |     8 |
|   1005 | b    |   1003 |     8 |
|   1006 | c    |   1003 |     8 |
|   1007 | d    |   1003 |     8 |
|   1008 | e    |   1003 |     8 |
|   1001 | x    |   1009 |    11 |
|   1002 | y    |   1009 |    11 |
|   1003 | z    |   1009 |    11 |
|   1004 | a    |   1009 |    11 |
|   1005 | b    |   1009 |    11 |
|   1006 | c    |   1009 |    11 |
|   1007 | d    |   1009 |    11 |
|   1008 | e    |   1009 |    11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)
ステップ 2: ON 句を実行して条件を満たさない行を除外します

ON i .userid = a.useridフィルタリング後の vt2 は次のようになります: 

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
ステップ 3: 外部行を追加するための JOIN

LEFT JOIN

は、vt2 に表示されない左側のテーブルから行を追加します。 vt2 に挿入すると、各行の残りのフィールドは NULL で埋められます。

RIGHT JOIN

同様に

この例では、LEFT JOIN が使用されるため、左側のテーブル user_info残りの行を追加してテーブル vt3 を生成します: 

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
ステップ 4: WHERE 条件フィルタリングWHERE a.userid = 1003 テーブル vt4 を生成します:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
Step 5: SELECT

SELECT i.name, a.money vt5 の生成: 

+------+-------+
| name | money |
+------+-------+
| z    |     8 |
+------+-------+
仮想テーブル vt5 が最終結果としてクライアントに返されます

終わりへの紹介テーブル処理の後、一般的に使用される

JOIN

4 の違いを見てみましょう。 INNER/LEFT/RIGHT/FULL JOIN

の違いを見てみましょう。

INNER JOIN. ..ON...

: 左右のテーブルで互いに一致するすべての行を返します (上記の ON フィルタリングの 2 番目のステップと、外部行を追加する 3 番目のステップのみが実行されるため)

  • LEFT JOIN...ON...: 左側のテーブルのすべての行を返します。右側のテーブルに対応する一致する行がない行がある場合は、右側のテーブルの列を新しいテーブルの NULL に変換します。
  • RIGHT JOIN...ON...: 右側のテーブルのすべての行を返します。一部の行に対応する一致がない場合は、左側のテーブルの行を追加するには、左側のテーブルの列を新しいテーブルに追加します。中央は NULL
  • INNER JOIN 3 番目のステップに進みます
    • 外部行の追加
例として、

LEFT JOIN

が置き換えられた場合、

INNER JOIN になった場合、この手順はスキップされ、生成されたテーブル vt3 は vt2 とまったく同じになります。 

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
RIGHT JOINLEFT JOIN を

RIGHT JOIN

に置き換えると、生成されるテーブル vt3 は次のようになります: 

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
+--------+------+--------+-------+
userid=1009 という行は user_account (右側のテーブル) には存在しますが、user_info (左側のテーブル) には見つからないため、この行のレコードはないため、次の行が 3 番目のステップで挿入されます: 
|   NULL | NULL |   1009 |    11 |

FULL JOIN

上文引用的文章中提到了标准SQL定义的FULL JOIN，这在mysql里是不支持的，不过我们可以通过LEFT JOIN + UNION + RIGHT JOIN 来实现FULL JOIN：

SELECT * 
  FROM user_info as i 
    RIGHT JOIN user_account as a 
      ON a.userid=i.userid
union 
SELECT * 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON a.userid=i.userid;

他会返回如下结果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

ps：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自mysql官方文档：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以当你纠结使用LEFT JOIN还是RIGHT JOIN时，尽可能只使用LEFT JOIN吧

5 ON和WHERE的区别

上文把JOIN的执行顺序了解清楚之后，ON和WHERE的区别也就很好理解了。
举例说明:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;
SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

第一种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加进虚拟表生成vt3即最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   NULL |  NULL |
|   1002 | y    |   NULL |  NULL |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

而第二种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid的行，生成表vt2；再执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，再对vt3表进行过滤生成vt4，得的最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

如果将上例的LEFT JOIN替换成INNER JOIN，不论将条件过滤放到ON还是WHERE里，结果都是一样的，因为INNER JOIN不会执行第三步添加外部行

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;
SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

想了解更多编程学习，敬请关注php培训栏目！

以上がMysqlのJOINの詳しい説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：
この記事はjianshu.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。
前の記事：mysqlマスタースレーブレプリケーション次の記事：mysqlマスタースレーブレプリケーション

関連記事

続きを見る