SQL join

数据库: mysql 5.6

1. 建表

 1 SET FOREIGN_KEY_CHECKS=0;
 2 
 3 -- role table --
 4 DROP TABLE IF EXISTS `role`;
 5 CREATE TABLE `role` (
 6   `id` int(11) NOT NULL AUTO_INCREMENT,
 7   `name` varchar(100) DEFAULT NULL,
 8   PRIMARY KEY (`id`)
 9 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
10 
11 INSERT INTO `role` VALUES ('1', '管理员');
12 INSERT INTO `role` VALUES ('2', '普通');
13 INSERT INTO `role` VALUES ('3', 'VIP');
14 
15 -- user table --
16 DROP TABLE IF EXISTS `user`;
17 CREATE TABLE `user` (
18   `id` int(11) NOT NULL AUTO_INCREMENT,
19   `name` varchar(100) NOT NULL,
20   `rid` int(11) DEFAULT NULL,
21   PRIMARY KEY (`id`)
22 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
23 
24 INSERT INTO `user` VALUES ('1', '张三', '1');
25 INSERT INTO `user` VALUES ('2', '李四', '2');
26 INSERT INTO `user` VALUES ('3', '王五', '1');
27 INSERT INTO `user` VALUES ('4', '赵六', null);

2. 各连接示意图(图片来源:SQL Joins as Venn Diagrams)

3. sql效果图

select * from user left join role on user.rid = role.id;

select * from user left join role on user.rid = role.id where role.id is null;

select * from user inner join role on user.rid = role.id;

select * from user right join role on user.rid = role.id;

select * from user right join role on user.rid = role.id where user.id is null;

Mysql不支持 FULL JOIN, 只能模拟了(参考: Full Outer Join in MySQL)

select * from user left join role on user.rid = role.id
union
select * from user right join role on user.rid = role.id;

select * from user left join role on user.rid = role.id where role.id is null
union
select * from user right join role on user.rid = role.id where user.rid is null;

PS:

mysql默认连接为内连接, select * from user join role on user.rid = role.id; 效果与内连接一致

关联查询时, 首先做笛卡尔积, 然后根据on, where等过滤