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等过滤
