数据准备

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
);

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
 

2、原始数据

# user_id, name, age
(1, "小赵", 21), 
(2, "小钱", 22), 
(3, "小孙", 23), 

  
 

  
1
  
2
  
3
  
4
 

将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);

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

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   |
+----+---------+------+
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

1、INNER JOIN（内连接）

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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
 

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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

4、UNION（全连接）

mysql 没有outer join 用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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
 

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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

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 |
+---------+------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
 

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 |
+---------+--------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

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 |
+----+---------+--------+----+---------+------+

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
 

总结

操作	关键字	解释	图示
内连接	INNER JOIN	$A\cap B$
左连接	LEFT JOIN	$a\in A$
右连接	RIGHT JOIN	$a\in B$
全连接	UNION	$A\cup B$
左表独有	LEFT JOIN WHERE	$A-A\cap B$
右表独有	RIGHT JOIN WHERE	$B-A\cap B$
并集去交集	UNION WHERE	$A\cup B-A\cap B$

参考
1、一张图看懂 SQL 的各种 join 用法
2、mysql中的几种join 及 full join问题

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/88665363