SQL：MySQL7種JOIN用法總結

資料準備

1、建2張表

# 姓名表
create table table_name(
    id int(11) primary key auto_increment,
    user_id int(11) default 0,
    name varchar(5) default ''
);

# 年齡表
create table table_age(
    id int(11) primary key auto_increment,
    user_id int(11) default 0,
    age int(11) default 0
);      

2、原始資料

# user_id, name, age
(1, "小趙", 21), 
(2, "小錢", 22), 
(3, "小孫", 23),       

将6條資料分為兩部分插入到資料庫中

# 名字表少一條 user_id = 3
insert into table_name(user_id, name)
values(1, "小趙"), (2, "小錢");

# 年齡表少一條 user_id = 2
insert into table_age(user_id, age)
values(1, 21), (3, 23);      

3、檢視資料

mysql> select * from table_name;
+----+---------+--------+
| id | user_id | name   |
+----+---------+--------+
|  1 |       1 | 小趙   |
|  2 |       2 | 小錢   |
+----+---------+--------+

mysql> select * from table_age;
+----+---------+------+
| id | user_id | age  |
+----+---------+------+
|  1 |       1 | 21   |
|  3 |       3 | 23   |
+----+---------+------+
      

mysql> select a.user_id, name, age  
    -> from table_name as a inner join table_age as b  
    -> on a.user_id=b.user_id;

+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       1 | 小趙   |   21 |
+---------+--------+------+      

2、LEFT JOIN （左連接配接）

mysql> select a.user_id, name, age
from table_name as a left join table_age as b
on a.user_id=b.user_id;
    
+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       1 | 小趙   |   21 |
|       2 | 小錢   | NULL |
+---------+--------+------+      

3、RIGHT JOIN（右連接配接）

mysql> select b.user_id, name, age
from table_name as a right join table_age as b
on a.user_id=b.user_id;

+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       1 | 小趙   |   21 |
|       3 | NULL   |   23 |
+---------+--------+------+      

4、UNION（全連接配接）

mysql> select a.user_id, name, age 
from table_name as a left join table_age as b
on a.user_id =b.user_id
union
select b.user_id, name, age 
from table_name as a right join table_age as b
on a.user_id =b.user_id;

+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       1 | 小趙   |   21 |
|       2 | 小錢   | NULL |
|       3 | NULL   |   23 |
+---------+--------+------+      

5、LEFT JOIN EXCLUDING INNER JOIN（左連接配接-内連接配接）

mysql> select a.user_id, name, age
    -> from table_name as a left join table_age as b
    -> on a.user_id=b.user_id
    -> where b.user_id is null;
    
+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       2 | 小錢   | NULL |
+---------+--------+------+      

6.RIGHT JOIN EXCLUDING INNER JOIN（右連接配接-内連接配接）

mysql> select b.user_id, name, age
    -> from table_name as a right join table_age as b
    -> on a.user_id=b.user_id
    -> where a.user_id is null;
+---------+------+------+
| user_id | name | age  |
+---------+------+------+
|       3 | NULL |   23 |
+---------+------+------+      

7、OUTER JOIN EXCLUDING INNER JOIN（外連接配接-内連接配接）

mysql> select a.user_id, name, age
    -> from table_name as a left join table_age as b
    -> on a.user_id =b.user_id
    -> where b.user_id is null
    -> union
    -> select b.user_id, name, age
    -> from table_name as a right join table_age as b
    -> on a.user_id =b.user_id
    -> where a.user_id is null;
    
+---------+--------+------+
| user_id | name   | age  |
+---------+--------+------+
|       2 | 小錢   | NULL |
|       3 | NULL   |   23 |
+---------+--------+------+      

8、笛卡爾積

mysql> select * from table_name join table_age;

+----+---------+--------+----+---------+------+
| id | user_id | name   | id | user_id | age  |
+----+---------+--------+----+---------+------+
|  1 |       1 | 小趙   |  1 |       1 |   21 |
|  2 |       2 | 小錢   |  1 |       1 |   21 |
|  1 |       1 | 小趙   |  2 |       3 |   23 |
|  2 |       2 | 小錢   |  2 |       3 |   23 |
+----+---------+--------+----+---------+------+      

總結

操作	關鍵字	解釋	圖示
内連接配接	INNER JOIN	A ∩ B A \cap BA∩B
左連接配接	LEFT JOIN	a ∈ A a \in Aa∈A
右連接配接	RIGHT JOIN	a ∈ B a \in Ba∈B
全連接配接	UNION	A ∪ B A \cup BA∪B
左表獨有	LEFT JOIN WHERE	A − A ∩ B A - A \cap BA−A∩B
右表獨有	RIGHT JOIN WHERE	B − A ∩ B B - A \cap BB−A∩B
并集去交集	UNION WHERE	A ∪ B − A ∩ B A \cup B - A \cap BA∪B−A∩B

參考

1、

一張圖看懂 SQL 的各種 join 用法

2、

mysql中的幾種join 及 full join問題