手把手教MySQL查询：语法、案例、真题 (7. 多表查询：内连接、左外连接、右外连接、全外连接、交叉连接)

7. 多表查询

1. 含义
	当查询的字段来自多个数据表时，需要使用多表查询

2. 笛卡尔乘积
	select 字段1,字段2,,
	from 表1,表2,,
	笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
	如何解决：添加有效的连接条件
	
3. 分类
	sql92
		内连接
	sql99
        内连接
            等值连接
            非等值连接
            自连接
        外连接
            左外连接
            右外连接
            全外连接（mysql不支持）
		交叉连接

4. 注意
	如果为表起了别名，查询的字段就不能用原来的表名去限定

7.1 内连接(sql92)

连接的过程为用一张表的每一行分别去连接第二张表的每一行，如果满足连接条件，则连接成功，否则不连接，最终返回的是两张表的交集部分

1. 等值连接
	* 使用2张表及以上，连接条件是等于
    -- 查询有奖金的每个部门的部门名、部门领导的编号、该部门的最低工资
    select department_name,manager_id,min(salary) 
    from depatments d,employees e 
    where d.department_id=e.department_id 
    and commission_pct is not null 
    group by department_name,manager_id; -- 这里用department_name,manager_id分组，因为题中没有说明部门与领导编号一一对应

2. 非等值连接
	* 使用2张表及以上，连接条件不是等于
    -- 查询员工的工资和工资级别
    select e.salary,j.grade_level
    from employees e,job_grages j
    where e.salary between j.lowest_sal and j.highest_sal;

3. 自连接
	* 使用1张表
	-- 查询员工名和上级的名称
	select e.last_name,m.last_name
	from employees e,employees m
	where e.manager_id=m.employee_id;

7.2 内连接（sql99）

1. 语法
	select 查询列表
	from 表1 【别名】 
	inner join 表2 【别名】
	on 连接条件
	【where 筛选条件】
	【GROUP BY 分组】
	【having 筛选条件】
	【order by 排序列表】
	
	* innner可以省略
	* 返回多表的交集
	* 表的顺序可以互换

2. 等值连接
    -- 查询员工名，部门名
    select last_name,edpartment_name
    from departments d 
    inner join employees e
    on e.department_id= d.department_id;

    -- 查询员工个数大于3的部门名和员工个数,按员工个数降序排序
    select department_name,count(*) 员工个数
    from employees e
    inner join departments d
    on e.department_id=d.department_id
    group by department_name  -- 注意，这里是不能用department_id
    having count(*)>3
    order by count(*) desc;

3. 非等值连接
	-- 查询员工的工资级别
	select salary,grade_level
	from employees e
	inner join job_grades g
	on e.salary between g.lowest_sal and g.highest_sal;

4. 自连接
 	-- 查询姓名中包含k的员工的名字和上级
 	select e.last_name 员工姓名,m.last_name 领导姓名
 	from employees e
 	inner join employees m
 	on e.manager_id=m.employee_id
 	where e.last_name like '%k%';

7.3 左/右外连接

1. 应用场景
	一个表中有，另一个表中没有

2. 特点
	* 外连接的查询结果为：
		主表中的所有记录+从表中有和它匹配的，则显示匹配的记录
            		  从表中没有和它匹配的，则显示NULL
		内连接的连接结果+主表中有从表中没有的记录
	* 主表与从表
		left join左边的是主表，右边的是从表
		rigth join右边的是主表，左边的是从表
		如果有多个表，判断哪个是主表：看要把哪个表中记录全部显示出来
		
3.案例
    -- 查询所有女神的男朋友（有的女神可能没男朋友）
    select b.name,bo.name
    from beauty b -- beauty是主表，因为要把所有女神的记录都查出来
    left join boys bo
    on b.boyfriend_id=bo.id;
   
    -- 查询哪个部门没有员工
    select d.department_name
    from departments d
    left join employees e
    on d.department_id=e.department_id
    where e.employ_id is null; -- 尽量选从表中的id列

7.4 全外连接

1. 语法
	select 查询列表
	from A
	FULL JOIN B
	ON A.key=B.key;
	
2. 连接结果
	全外连接=内连接的结果+表1中有表2中没有+表2中有表1中没有

3. mysql不支持

7.5 交叉连接

1. 语法
	select 查询列表
	from A
	cross join B;
	
	返回的结果：笛卡尔乘积
	
2. 案例
	select  b.*,bo.*
	from beauty b
	cross join boys bo; -- 没有on连接条件语句