mysql多表连接查询

参考文章:MYSQL语法：左连接、右连接、内连接、全外连接

多表连接查询
两张表按照指定条件,连接成一张表
从连接的结果表中查询
use hr;

select * from depts;

-- 查询员工,显示员工的部门名   (一个员工没有部门被过滤掉了)
select e.id,e.fname,d.dept_id,d.dept_name
from emps e,depts d
where e.dept_id=d.dept_id;

-- 地区表
select * from locations;

-- 查询部门depts,显示部门的城市locations
select d.loc_id,l.city
from depts d,locations l
where d.loc_id=l.loc_id;

-- 查询部门mgr_id,显示部门经理名 (显示部门经理名,其他普通员工过滤掉) (经典面试题)
select d.dept_name,e.fname
from depts d,emps e
where d.mgr_id=e.id;

-- 查询员工,显示员工主管名
-- 自连接,一张表,看做是两张表来连接 106条/107条
-- (e1当作员工表,e2当作主管表 e1.fname或e2.fanme任意一个起别名作为主管name )
select e1.fname,e2.fname mgr
from emps e1,emps e2
where e1.mgr_id=e2.id;

内连接:只查询满足连接条件的数据,不满足连接条件的数据不会查询
(笛卡尔积,两条表每条都有对应的记录)

-- 外连接:不满足连接条件的数据也要查询
左外连接:查询左侧表条件外的数据(左边出现的都要出现)
右外连接:查询右侧表条件外的数据(右边出现的都要出现)
全外连接:双侧表条件外数据,Mysql不支持全外连接(不支持full join/full outer join)

--外连接,非标准sql连接语句,每种数据库连接语法都不同 
-- sqlserver (非标准语法)
-- where a.id(+)=b.id 左外连接
-- where a.id=b.id(+) 右外连接

--oracle 相反 (非标准语法)
-- where a.id =* b.id 左外连接
-- where a.id *= b.id 右外连接

--mysql没有外连接的非标准语法  (非标准语法)

标准的表连接语法(所有数据库都支持) ( (标准语法))
内连接  on取代了where; inner join on 中的inner可以省略
select ...
from a join b
on (a.id=b.id)
join c
on(...)

左外连接
select ...
from a left join b
on (a.id=b.id)

右外连接
select ...
from a right join b
on (a.id=b.id)

-- 查询所有部门,显示部门经理 (16条规划中的部门,没有经理显示null)
-- 27个部门名称
-- 11个部门经理
-- 16条规划中的部门没有经理
select d.dept_name,e.fname
from depts d left join emps e
on(d.mgr_id=e.id);
-- 或者:
select e.fname,d.dept_name 
from emps e right join depts d 
on(e.id=d.mgr_id);

-- 查询所有部门,显示部门经理
-- 112个数据
-- 11个部门,有经理
-- 16条规划中的部门没有经理
-- 85个员工可能是经理,无对应部门
-- 实现全外连接使用的效果的方法:
select e.fname,d.dept_name from emps e right join depts d on e.id=d.mgr_id
union 
select e.fname,d.dept_name from emps e left join depts d on e.id=d.mgr_id;

-- 查询107个员工,显示部门名和城市 (三表联查)(经典面试题)
select e.fname,d.dept_name,d.loc_id
from emps e
left join depts d
on(e.dept_id=d.dept_id);

select e.fname,d.dept_name,d.loc_id,l.loc_id,l.city
from emps e
left join depts d
on(e.dept_id=d.dept_id)
left join locations l
on(d.loc_id=l.loc_id);