mysql 表间关联查询

Mysql 表间关联查询

内连接

1. 语法: 表1 [inner] join 表2 on 条件 // inner可以省略
   说明: on 关联条件 表1–>驱动表, 表2–> 匹配表
2. 执行过程: 遍历驱动表,在匹配表中匹配记录
3. 等值连接时 (条件中使用’='): 表1,表2 位置可以交换, 驱动表在前,匹配表灾后
4. 结果集特点:
   能够在两张表中找到的记录(满足条件/匹配上)会被保留, 找不到(不满足条件/匹配不上)的会被丢弃

• 查询员工的姓名和其部门的名字

-- 表关联查询
select ename,dept_name, e.deptno -- 若字段在两张表中重名则需要指名是哪个表的字段(若不重名则不用指定)
from emp_lym e inner join dept_lym d on e.deptno=d.dept_id;

笛卡尔积:

3. 不加where条件的连接,查询到的是笛卡尔积的结果。
4. 检索结果的记录条数 = 第一张表的记录条数 x 第二张表的记录条数

-- 查询员工的姓名和其部门的名字 (其他写法)
-- 先笛卡尔积,再筛选
select ename,dept_name,e.deptno
from emp_lym e , dept_lym d 
where e.deptno=d.dept_id;  -- 等价于 交叉连接(cross join)
-- 效率低

交叉连接 cross join

-- 查询员工的姓名和其部门的名字 (其他写法)
-- 交叉连接
select ename,dept_name,e.deptno
from emp_lym e(也叫'驱动表')  cross join dept_lym d(也叫'匹配表') 
where e.deptno=d.dept_id;

• 查询员工的姓名和其领导的名字

-- 分析 员工的leader字段等于领导的员工号
select e1.ename,e1.leader,e2.ename,e2.empno
from emp_lym e1 join emp_lym e2
on e1.leader=e2.empno;

• 查询员工的姓名和其部门的名字,要求没有部门的员工也被查出来

-- 分析: 组合查询 查询的是全部员工 = 有部门的(内连接查询) + 没有部门的(基础查询)
-- 有部门的
select ename,dept_name
from emp_lym e , dept_lym d 
where e.deptno=d.dept_id;
-- 没有部门的
select ename, "not dept"
from emp_lym
where depetno is  null;
-- 组合
select ename,dept_name
from emp_lym e , dept_lym d 
where e.deptno=d.dept_id
UNION
select ename, "not dept"
from emp_lym
where deptno is not null;

内连接的结果集的中数据一定是在两张表中,能够找到的匹配记录 (忽略null);

外连接

语法: 严格区分,驱动表与匹配表(不能随意互换位置) 驱动表的数据会全部显示
左右外连接可以互换 但是必须明确那个表是驱动表

1)左外链接: 左边的表是驱动表

​ 语法：表1 left join 表2 on 条件

2)右外连接: 右边的表是驱动表

语法： 表1 right join 表2 on 条件

• 查询员工的姓名和其部门的名字,要求没有部门的员工也被查出来

 -- 分析 查询全部的员工, 员工表中的数据全部出现在外连接的结果集中---->作为驱动表
 select ename,ifnull(dept_name,"no dept")
 from emp_lym e left outer join dept_lym d
 on e.deptno=d.dept_id;

• 查询员工姓名和其部门的名字,要求没有员工部门的也要被查处来

 -- 分析 查询全部的部门信息--->部门表为驱动表
 select ename,ifnull(dept_name,"no dept")
 from  dept_lym d left outer join emp_lym e 
 on e.deptno=d.dept_id;

外连接特点:

​ 如果驱动报表在匹配表中找不到匹配记录,则匹配一行空值 ，但是保证驱动表中数据全部出现在外链接的结果集中
PS: 外连接的结果集=内连接的结果集+驱动表在匹配表中匹配不上的记录
需要查询全部数据,----> 外连接---->确定驱动表(那个表的数据需要全部出现在外连接的结果集中,就作为驱动表
注意: 不要关联不必要的表
​ 处理关联消耗资源,关联的表越多会导致性能下降
​ 处理同样的结果可能存在很多实现方式,寻找最优的
补充:

全外连接: ----mysql 不支持

语法: 表1 full join 表2 on 条件

• 查询全部员工和全部部门

select empno,ename,d.deptno,dept_name
from emp_lym e full join -- mysql 不支持
dept_lym d
on e.deptno=d.deptno;

mysql 实现类似全外连接的方式

select empno,ename,d.dept_id,d.dept_name
from emp_lym e left join 
dept_lym d
on e.deptno=d.dept_id
union
select empno,ename,d.dept_id,d.dept_name
from emp_lym e right join 
dept_lym d
on e.deptno=d.dept_id;