数据准备
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 |
参考
