msyq 数据库 表内连接 外连接 全连接 基本操作
准备：
角色表：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '超级管理员');
INSERT INTO `role` VALUES (2, '管理员');
INSERT INTO `role` VALUES (3, '员工');
SET FOREIGN_KEY_CHECKS = 1;
角色菜单表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role_menu
-- ----------------------------
DROP TABLE IF EXISTS `role_menu`;
CREATE TABLE `role_menu` (
`role_id` int(11) NOT NULL,
`menu_id` int(11) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role_menu
-- ----------------------------
INSERT INTO `role_menu` VALUES (1, 3);
INSERT INTO `role_menu` VALUES (1, 2);
INSERT INTO `role_menu` VALUES (1, 4);
INSERT INTO `role_menu` VALUES (1, 6);
INSERT INTO `role_menu` VALUES (1, 7);
INSERT INTO `role_menu` VALUES (1, 9);
INSERT INTO `role_menu` VALUES (10, 10);
SET FOREIGN_KEY_CHECKS = 1;
一．Join语法概述
join 用于多表中字段之间的联系，语法如下：
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1:左表；table2:右表。
JOIN 按照功能大致分为如下三类：
- INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
- LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
- RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
二.Inner join
内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。
select *from role a innser join role_menu b on a.id=b.role_id
三.Left join
（1）
select *from role a left join role_menu b on a.id=b.role_id
left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。
（2）
如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下：
select *from role a left join role_menu b on a.id=b.role_id where b.role_id is null
（3）求差集：
根据上面的例子可以求差集，如下：
select *from role a left join role_menu b on a.id=b.role_id where b.role_id is null
union
select *from role a right join role_menu b on a.id=b.role_id where a.id is null
四.Right join
mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id | name | id | name |
+------+--------+----+-------------+
| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vader |
| 3 | Ninja | 4 | Ninja |
+------+--------+----+-------------+
4 rows in set (0.00 sec)同left join。
五.Cross join
cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积
笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name | id | name |
+----+-----------+----+-------------+
| 1 | Pirate | 1 | Rutabaga |
| 2 | Monkey | 1 | Rutabaga |
| 3 | Ninja | 1 | Rutabaga |
| 4 | Spaghetti | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | 2 | Pirate |
| 3 | Ninja | 2 | Pirate |
| 4 | Spaghetti | 2 | Pirate |
| 1 | Pirate | 3 | Darth Vader |
| 2 | Monkey | 3 | Darth Vader |
| 3 | Ninja | 3 | Darth Vader |
| 4 | Spaghetti | 3 | Darth Vader |
| 1 | Pirate | 4 | Ninja |
| 2 | Monkey | 4 | Ninja |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | 4 | Ninja |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)
#再执行：mysql> select * from A inner join B; 试一试 #在执行mysql> select * from A cross join B on A.name = B.name; 试一试
实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字，因此下面的 SQL 效果是一样的：
... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2
六.Full join（取并集）
（1）
select *from role a left join role_menu b on a.id=b.role_id
union all
select *from role a right join role_menu b on a.id=b.role_id
（2）
select *from role a left join role_menu b on a.id=b.role_id
union
select *from role a right join role_menu b on a.id=b.role_id
（1）和（2）的区别：union和union all 的区别就是 交集部分重复的是否去重全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。
