DQL语言学习进阶六（连接查询）
一、含义
连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
二、笛卡尔乘积现象
当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
表1有m行，表2有n行，结果m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
三、分类
（一） 按年代分类
sql92标准（仅支持内连接）
sql99标准【推荐】
（二） 按功能分类
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接（MySQL不支持）
交叉连接
一、sql92标准
1、等值连接
语法：
select 查询列表 from 表1 别名，表2，别名 where 表1.key= 表2.key【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
特点：等值标准：
（1）多表等值连接的结果为多表的交集部分
（2）n表连接，至少需要n-1个连接条件
（3）多表的顺序没有要求
（4）一般需要为表起别名
（5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
例1：查询女神名和对应的男神名
select name，boyname from boys, beauty where beauty.boyfriend_id= boys.id;
例2：查询员工名和对应的部门名
select last_name, department_name from employee, department where employee. 'department_id'=department.'department_id';
为表起别名
（1）提高语句简洁度
（2）区分多个重名的字段
注：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
例：查询员工号，工种号，工种名
select e.last_name, e.job_id, j.job_titlefrom employee e, job j where e.'job_id'= j.'job_id';
两个表的顺序可以调换
可以添加筛选
例1、查询有奖金的员工名，部门名
select last_name,department_name from employee e, department d where e.'department_id'= d.'department_id'and e.'commssion_pct'isnotnull;
例2、查询城市名中第二个字段为o的部门名和城市名
select department_id, city from department d, location l where d.'location_id'= l.'location_id'and city like'_o%';
可以加分组
例1、查询每个城市的部门数
selectcount(*) 个数,city from department d, location l where d.'location_id'= l.'location_id'groupby city;
可以加函数
例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_id,count(*)from employee e, job.jwhere e.'job_id'= j.'job_id'groupby job_title orderbycount(*)desc;
可以实现三表连接
例：查询员工名，部门名和所在的城市
select last_name,department_name,city from employee e, department d, location l where e.'department_id'= d.'department_id'and d.'department_id'= l.'department_id'and city like'a%';
2、非等值连接
语法：
select 查询列表 from 表1 别名，表2，别名 where 非等值的连接条件 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
例1：查询员工的工资和工资级别
select salary，grade_level from employee e, grade g where salary between g.'loweat_sal'and g.'higheat_sal'and g.'grade_level'='A';
3、自连接
语法：
select 查询列表 from 表 别名1，表，别名2 where 等值的连接条件 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
例：查询员工名和上级的名称
select e. employee_id, e.last_name, m.memployee_id, m.last_namefrom employee e, employee m where e.'manager_id'= m.'manger_id';
二、sql99语法
语法：
select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 分组】 【having 筛选条件】 【order by 排序列表】;
内连接：inner
外连接（左外：left【outer】、右外：right【outer】、全外：full【outer】）
交叉连接：cross
（一）内连接
1、语法：
select 查询列表 from 表1 别名 【inner】 join 表2 别名 on 连接条件 where 筛选条件 groupby 分组列表 having 分组后的筛选 orderby 排序列表 limit 子句;
2、分类：
等值连接
非等值连接
自连接连接
3、特点：
（1）添加排序、分组、筛选
（2）inner可以省略
（3）筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
（4）inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
（表的顺序可以调换
内连接的结果=多表的交集
n表连接至少需要n-1个连接条件）
1、等值连接
例1：查询员工名、部门名
select last_name, department_name from employee e inner join department d on e.'department_id'= d.'department_id';
例2：查询名字中包含a的员工名和工种名（添加筛选）
select last_name, job_title from employee e inner join job j on e.'job_id'= j.'job_id'where e.'last_name'like'%a%';
例3、查询部门个数大于3的城市名和部门个数（添加分组+筛选）
（1）查询每个城市的部门个数
（2）在（1）的基础上筛选满足条件
select city，count(*) 部门个数 from department d inner join location l on d.'location_id'= l.'location_id'groupby city havingcount(*)>3;
例4、查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
（1）查询每个部门的员工个数
selectcount(*)，department_name from employee e inner join department d on e.'department_id'= d.'department_id'groupby department;
（2）在（1）的结果上筛选员工个数>3的记录，并排序
selectcount(*)个数，department_id from employee e inner join department d on e.'department_id'= d.'department_id'groupby department_name havingcount(*)>3orderbycount(*)desc;
例5、查询员工名、部门名、工种名，并按部门名排序
select last_name, department_name, job_title from employee e inner join department d on e.'department_id'= d.'department_id'inner join job j on e.'job_id'= j.'job_id'orderby department_name desc;
2、非等值连接
例：查询员工的工资级别
select salary,grade_level from employee e join job_graded g on e.'salary'between g.'lowest_sal'and g.'hightest_sal';
3、自连接
例：查询员工的名字、上级的名字
select e.last_name, m.last_namefrom employee e join employee m on e.'manager_id'= m.'employee_id';
（二）外连接
1、应用场景：用于查询一个表中有，另一个表没有的记录
2、语法：
select 查询列表 from 表1 别名 left/right/full【outer】 join 表2 别名 on 连接条件 where 筛选条件 groupby 分组列表 having 分组后的筛选 orderby 排序列表 limit 子句；
3、特点：
（1）外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
（2）左外连接：left join 左边的是主表
右外连接：right join 右边的是主表
全外连接：full join 两边都是主表
（3）左外和右外交换两个表的顺序，可以实现同样的效果
（4）全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的
（5）一般用于查询除了交集部分的剩余的不匹配的行
例1：查询男友不在男生表的女生名
左外连接：
select b.name, bo.*from beauty b left outer join boy bo on b.'boyfrind_id'= bo.'id';
右外连接：
select b.name, bo.*from boy bo left outer join beauty b on bo.'id'= b.'boyfrind_id';
例2：查询哪个部门没有员工
左外：
select d.*, e.employee_idfrom department d left outer join employee e on d.'department_id'= e.'department_id'where e.'employee_id'isnull;
右外：
select d.*, e.employee_idfrom employee e left outer join department d on d.'department_id'= e.'department_id'where e.'employee_id'isnull;
全外连接：
use girl select b.*, bo.*from beauty b full outer join boy bo on b.'boyfrind_id'= bo.id;
交叉连接：
语法：
select 查询列表 from 表1 别名 cross join 表2 别名；
特点：类似于笛卡尔乘积
select b.*, bo.*from beauty b cross join boy bo;
sql92 PK sql99
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
