MySQL-Join

MysSQL连接分类

分类

1. 内连接(inner join)：表a和表b之间的每一行基于对应id列连接后，只取出连接表中匹配的数据，匹配不到的不保留。
2. 外连接：表a和表b之间的每一行基于对应id列连接后，匹配不到的也会保留，值为NULL。 外连接可以再被划分为左连接和右连接。

实例

A表

select * from A;

---

id | name
 1 |  A1
 2 |  A2
 3 |  A3
 4 |  A4

B表

select * from B; // 缺少a_id=4

---

id | age | a_id 
 1 |  11 |  1
 2 |  22 |  3
 3 |  33 |  2
 6 |  66 |  6

内连接（inner join）
selcct * from A as a inner join B as b on a.id= b.a_id;

selcct * from A as a inner join B as b on a.id= b.a_id;

---

id | name | id | age | a_id 
 1 |  A1  |  1 |  11 |  1
 2 |  A2  |  3 |  33 |  2                
 3 |  A3  |  2 |  22 |  3

只会保留有对应匹配的连接表中的行

外连接（outer join）

• 左连接（left outer join）：以左边的表为主表
• 右连接（right outer join）：以右边的表为主表

左连接
A表

select * from A;

---

id | name
 1 |  A1
 2 |  A2
 3 |  A3
 4 |  A4

B表

select * from B; // 缺少a_id=4 故意错位a_id和b.id 的2 和 3

---

id | age | a_id 
 1 |  11 |  1
 2 |  22 |  3
 3 |  33 |  2
 6 |  66 |  6

---

selcct * from A as a left join B as b on a.id= b.a_id;

selcct * from A as a left join B as b on a.id= b.a_id;

---

id | name | id | age | a_id 
 1 |  A1  |  1 |  11 |  1
 2 |  A2  |  3 |  33 |  2                
 3 |  A3  |  2 |  22 |  3
 4 |  A4  |  NULL | NULL | NULL 

右连接

---

selcct * from A as a right join B as b on a.id= b.a_id;

selcct * from A as a right join B as b on a.id= b.a_id;

---

id |  name | id | age | a_id |  
 1 |  A1   |  1 |  11 |  1   |      
 3 |  A2   |  2 |  33 |  3   |                 
 2 |  A3   |  3 |  22 |  2   |  
 NULL| NULL |  6 | 66 |  6   |