MySQL 连接查询

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, ‘刘备‘, 3);
INSERT INTO `emp` VALUES (2, ‘关羽‘, 3);
INSERT INTO `emp` VALUES (3, ‘张飞‘, 3);
INSERT INTO `emp` VALUES (4, ‘张角‘, 2);
INSERT INTO `emp` VALUES (5, ‘张宝‘, 2);
INSERT INTO `emp` VALUES (6, ‘张梁‘, 2);
INSERT INTO `emp` VALUES (7, ‘曹操‘, 1);
INSERT INTO `emp` VALUES (8, ‘曹丕‘, 1);
INSERT INTO `emp` VALUES (9, ‘曹植‘, 1);
INSERT INTO `emp` VALUES (10, ‘曹冲‘, 1);
INSERT INTO `emp` VALUES (11, ‘孙权‘, 0);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, ‘人力资源部‘);
INSERT INTO `dept` VALUES (2, ‘财务部‘);
INSERT INTO `dept` VALUES (3, ‘后勤部‘);
INSERT INTO `dept` VALUES (4, ‘法务部‘);

SET FOREIGN_KEY_CHECKS = 1;

select * from dept;
select * from emp;
-- 交叉连接 
select * from dept,emp; -- 44条数据 笛卡尔积 a表的长度 * b表的长度
select * from dept cross join emp;  -- 笛卡尔积在查询的时候没有太大的意义，在数学上表示所有可能性
-- 内连接是查询出满足条件的数据
--  inner join 隐式内连接
select * from emp as a,dept as b where a.dept_id=b.id;
-- 显式内连接 
select * from emp as a join dept as b on a.dept_id = b.id;
-- outer join 外连接(包含左外连接和右外连接)
-- 左外连接(以左表为主表，右表为从表)
-- 主表全部显示，从表只有匹配到才显示，如果没有匹配到就显示null
select * from emp a left join dept b on a.dept_id=b.id;
-- 右外连接(以右表为主表，左表为从表)
select * from emp a right join dept b on a.dept_id=b.id;

select * from emp as a join dept as b on a.dept_id = b.id;  -- 显式内连接

select * from emp as a left join dept as b on a.dept_id = b.id;

select * from emp as a right join dept as b on a.dept_id = b.id;

select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null;

select * from emp as a right join dept as b on a.dept_id = b.id where a.id is null;

-- union 全连接
select * from emp as a left join dept as b on a.dept_id = b.id
union -- union 自动去重
select * from emp as a right join dept as b on a.dept_id = b.id;

select * from emp as a left join dept as b on a.dept_id = b.id
union all -- union all 不会自动去重
select * from emp as a right join dept as b on a.dept_id = b.id;

select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null
union
select * from emp as a right join dept as b on a.dept_id = b.id where a.dept_id is null;

-- full join 的用法
select a.id,a.name,a.dept_id,b.id,b.name from emp as a full join dept as b on a.dept_id = b.id;