数据准备

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

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

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

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\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问题