MySQL JOINS

建表

学生表student (学生编号id，姓名nama，年龄age，部门编号dept_id）
部门表dept (部门编号dept_id，部门名称dept_name，部门位置address）

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `dept_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
 
INSERT INTO `student` VALUES (1, '张三', 20, '1');
INSERT INTO `student` VALUES (2, '李四', 23, '1');
INSERT INTO `student` VALUES (3, '王五', 25, '2');
INSERT INTO `student` VALUES (4, '赵柳', 20, '2');
INSERT INTO `student` VALUES (5, '张无忌', 21, '3');
INSERT INTO `student` VALUES (6, '赵敏', 18, '3');
INSERT INTO `student` VALUES (7, '唐三', 19, '3');
INSERT INTO `student` VALUES (8, '赵英俊', 50, '3');
INSERT INTO `student` VALUES (9, '无名', 72, '4');
INSERT INTO `student` VALUES (10, '小舞', 18, '3');
INSERT INTO `student` VALUES (11, '红毛', 17, '3');
INSERT INTO `student` VALUES (12, '叶凡', 20, NULL);
 
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `dept_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '部门主键',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
 
INSERT INTO `dept` VALUES (1, '社会部', '一楼大厅');
INSERT INTO `dept` VALUES (2, '小说部', '二楼会议室');
INSERT INTO `dept` VALUES (3, '动漫部', '三楼小广场');
INSERT INTO `dept` VALUES (4, '其他部', '楼顶办公室');
INSERT INTO `dept` VALUES (5, '市场部', '室外活动室');

概览

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE
JOIN 含义：把两张表的属性通过其值组合在一起
连接可以替换子查询，并且比子查询的效率一般会更快

1. 交叉连接

cross join (笛卡尔连接/叉乘) 不带 ON 子句，返回两表乘积(笛卡尔积)

• 隐示

  select *
  from student s, dept d
  where s.dept_id=d.dept_id
  

• 显式

  select *
  from student s cross join dept d
  where s.dept_id=d.dept_id
  

  

2. 内连接

[inner] join (等值连接) 返回连接表(数据库查询时形成的中间表)中符合连接条件和查询条件的数据行

• 隐示
  中间表：两表笛卡尔积

  select *
  from student s, dept d
  where s.dept_id=d.dept_id
  

• 显式
  中间表：两表经 ON 条件过滤后的笛卡尔积
  

  select *
  from student s inner join dept d
  on s.dept_id=d.dept_id
  

  

  尽量使用 join 而不是 where 来列出关联条件，特别是多个表联合时，原因是：

  • 在效率上，where 可能具有和 join 一样的效率，但 join 的效率不比 where 差
  • 使用 join 可帮助检查语句中的无效或者误写的关联条件
  • 多表使用 left join 只是把主表里的所有数据查询出来，其他表只查询表中的符合条件的某一条记录，所以速度非常快；而多表使用 where 内联，是把所有表的数据全查出来，然后进行比对，所以速度非常慢

自连接(连接的表是自身)
找出与“唐三”同部门的所有同学姓名

• 子查询版本

  select `name`
  from student
  where dept_id=(
  	select dept_id
  	from student
  	where `name`='唐三');
  

• 自连接版本

  select s1.`name`
  from student s1 inner join student s2
  on s1.dept_id=s2.dept_id
  and s2.`name`='唐三'
  

  

3. 自然连接

natural [inner] join 把同名列通过等值测试连接起来的，同名列可有多个
不带 ON 或 WHERE. 当两个关系无公共属性时，自然连接就转化成笛卡尔积
内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列

select * from student natural join dept

4. 外连接

保留没有关联的行

左[外]连接

left [outer] join 保留左表没有关联的行/左边的都要在

select *
from student s left join dept d
on s.dept_id=d.dept_id

左表独有

select *
from student s left join dept d
on s.dept_id=d.dept_id
where d.dept_id is null

右[外]连接

right [outer] join 保留右表没有关联的行/右边的都要在

select *
from student s right join dept d
on s.dept_id=d.dept_id

右表独有

select *
from student s right join dept d
on s.dept_id=d.dept_id
where s.dept_id is null

全[外]连接

full [outer] join MySQL不支持 Oracle支持 -> 左连接+union+外连接
结合了 left join 和 right join 的结果(只要左右表其中一个表中存在匹配，就返回行)

select * from student s left join dept d on s.dept_id=d.dept_id
union
select * from student s right join dept d on s.dept_id=d.dept_id

UNION 组合查询

• 用 UNION 组合2个查询，若第1个查询返回 M 行，第2个查询返回 N 行，则组合查询结果一般为 M+N 行
• 每个查询必须包含相同的列、表达式和聚集函数
• 默认会去除相同行，如果需要保留相同行，使用 UNION ALL
• 只能包含一个 ORDER BY 子句，并且必须位于语句的最后

并集去交集

2表无共同数据

select * from student s left join dept d on s.dept_id=d.dept_id where d.dept_id is null
union
select * from student s right join dept d on s.dept_id=d.dept_id  where s.dept_id is null

比较 笛卡尔积、等值连接、自然连接

DROP TABLE IF EXISTS R;
CREATE TABLE R (
  A int(5) NOT NULL,
  B int(5) NOT NULL,
  C int(5) NOT NULL
);
INSERT INTO R VALUES (3,6,7);
INSERT INTO R VALUES (2,5,7);
INSERT INTO R VALUES (7,2,3);
INSERT INTO R VALUES (3,6,7);

DROP TABLE IF EXISTS S;
CREATE TABLE S (
  A int(5) NOT NULL,
  B int(5) NOT NULL,
  C int(5) NOT NULL
);
INSERT INTO S VALUES (3,4,5);
INSERT INTO S VALUES (7,2,3);

R表r列m行 S表s列n行

连接	关系代数	元数(列)	元组数(行)
笛卡尔积（基本操作）	$R\times S$	r + s	mn
等值连接（组合操作）	$R\underset{1=2}{\bowtie }S={\sigma }_{R.A=S.B}(R\times S)$	r + s	0 ~ mn
自然连接（组合操作）	$R\bowtie S={\Pi }_{R\cup S}{\sigma }_{R.A=S.A}(R\times S)$	r + s - 公共列数	0 ~ mn

3种连接比较

1. 笛卡尔积对两个关系R和S进行操作，产生的关系中元组个数为两个关系中元组个数之积
2. 等值连接则是在笛卡尔积的结果上再进行选择操作，挑选关系第 i 个分量与第(r+j) 个分量值相等的元组
3. 自然连接则是在等值连接(以公共属性值相等为条件)的基础上再行投影操作，去掉 S 中的公共属性列，当两个关系没有公共属性时，自然连接就转化成笛卡尔积

等值连接 & 自然连接

• 自然连接一定是等值连接，但等值连接不一定是自然连接
• 等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性
• 等值连接不除去重复属性；而自然连接会除去重复属性

注意

ON 条件

牛客 SQL74 考试分数(三)

两表连接查询的选择

1. 查两表关联列相等的数据->内连接
2. Col_L是Col_R子集->右连接
3. Col_R是Col_L子集->左连接
4. Col_R和Col_L彼此有交集但彼此互不为子集->全连接
5. 求差操作->联合查询
   多表查询->不同连接类型可写到一块