1 连接种类
2 INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
3 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
4 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
5 OUT JOIN 全连接 将左边表数据和右边表数据全部匹配出来
6 ---------------------------------------------------------------------------------------
7 ---------------------------------------------------------------------------------------
8
9 -- 示例
10 SELECT * FROM tcount_tbl;
11 +---------------+--------------+
12 | runoob_author | runoob_count |
13 +---------------+--------------+
14 | 菜鸟教程 | 10 |
15 | RUNOOB.COM | 20 |
16 | Google | 22 |
17 +---------------+--------------+
18 SELECT * from runoob_tbl;
19 +-----------+---------------+---------------+-----------------+
20 | runoob_id | runoob_title | runoob_author | submission_date |
21 +-----------+---------------+---------------+-----------------+
22 | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
23 | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
24 | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
25 | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
26 | 5 | 学习 C | FK | 2017-04-05 |
27 +-----------+---------------+---------------+-----------------+
28
29 ---------------------------------------------------------------------------------------
30 ---------------------------------------------------------------------------------------
31
32 INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录
33 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a
34 INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
35 +-------------+-----------------+----------------+
36 | a.runoob_id | a.runoob_author | b.runoob_count |
37 +-------------+-----------------+----------------+
38 | 1 | 菜鸟教程 | 10 |
39 | 2 | 菜鸟教程 | 10 |
40 | 3 | RUNOOB.COM | 20 |
41 | 4 | RUNOOB.COM | 20 |
42 +-------------+-----------------+----------------+
43 -- 也可以这样使用
44 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
45 +-------------+-----------------+----------------+
46 | a.runoob_id | a.runoob_author | b.runoob_count |
47 +-------------+-----------------+----------------+
48 | 1 | 菜鸟教程 | 10 |
49 | 2 | 菜鸟教程 | 10 |
50 | 3 | RUNOOB.COM | 20 |
51 | 4 | RUNOOB.COM | 20 |
52 +-------------+-----------------+----------------+
53 ---------------------------------------------------------------------------------------
54 ---------------------------------------------------------------------------------------
55
56 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
57 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
58 +-------------+-----------------+----------------+
59 | a.runoob_id | a.runoob_author | b.runoob_count |
60 +-------------+-----------------+----------------+
61 | 1 | 菜鸟教程 | 10 |
62 | 2 | 菜鸟教程 | 10 |
63 | 3 | RUNOOB.COM | 20 |
64 | 4 | RUNOOB.COM | 20 |
65 | 5 | FK | NULL |
66 +-------------+-----------------+----------------+
67
68 -- 也可以这样
69 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author(+); （+在那边就显示那边的null）
70 +-------------+-----------------+----------------+
71 | a.runoob_id | a.runoob_author | b.runoob_count |
72 +-------------+-----------------+----------------+
73 | 1 | 菜鸟教程 | 10 |
74 | 2 | 菜鸟教程 | 10 |
75 | 3 | RUNOOB.COM | 20 |
76 | 4 | RUNOOB.COM | 20 |
77 | 5 | FK | NULL |
78 +-------------+-----------------+----------------+
79 ---------------------------------------------------------------------------------------
80 ---------------------------------------------------------------------------------------
81
82
83 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
84
85 RIGHT JOIN
86 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
87 +-------------+-----------------+----------------+
88 | a.runoob_id | a.runoob_author | b.runoob_count |
89 +-------------+-----------------+----------------+
90 | 1 | 菜鸟教程 | 10 |
91 | 2 | 菜鸟教程 | 10 |
92 | 3 | RUNOOB.COM | 20 |
93 | 4 | RUNOOB.COM | 20 |
94 | NULL | NULL | 22 |
95 +-------------+-----------------+----------------+
96 -- 也可以这样
97 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a , tcount_tbl b ON a.runoob_author(+) = b.runoob_author;
98 +-------------+-----------------+----------------+
99 | a.runoob_id | a.runoob_author | b.runoob_count |
100 +-------------+-----------------+----------------+
101 | 1 | 菜鸟教程 | 10 |
102 | 2 | 菜鸟教程 | 10 |
103 | 3 | RUNOOB.COM | 20 |
104 | 4 | RUNOOB.COM | 20 |
105 | NULL | NULL | 22 |
106 +-------------+-----------------+----------------+
107 ---------------------------------------------------------------------------------------
108 ---------------------------------------------------------------------------------------
109
110 OUT JOIN 全连接
111 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a OUT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
112 +-------------+-----------------+----------------+
113 | a.runoob_id | a.runoob_author | b.runoob_count |
114 +-------------+-----------------+----------------+
115 | 1 | 菜鸟教程 | 10 |
116 | 2 | 菜鸟教程 | 10 |
117 | 3 | RUNOOB.COM | 20 |
118 | 4 | RUNOOB.COM | 20 |
119 | 5 | FK | NULL |
120 | NULL | NULL | 22 |
121 +-------------+-----------------+----------------+
122 ---------------------------------------------------------------------------------------
123 ---------------------------------------------------------------------------------------