/*
语法：select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件 】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
内连接：inner
外连接
左外:left [outer]
右外:right [outer]
全外: full [outer]
交叉连接:cross

*/

一、内连接

/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；
分类：
等值
非等值
自连接
特点：
一：添加排序、分组、筛选
二：inner可以省略
三：筛选条件放在where后面，
连接条件放在on后面，提高分离性，便于阅读
四：inner join连接和sql92语法中的
等值连接效果是一样的，都是查询多表的交集

*/

1.等值连接

案例：

查询员工名、部门名

select last_name,department_name 
from employees e 
inner join departments d 
on e.department_id = d.department_id;

查询名字中包含员工名和工种名

select last_name,job_title 
from employees e 
inner join jobs j 
on e.job_id = j.job_id 
where job_title like '%e%';

查询部门个数大于3的城市名称和部门个数（分组+筛选）

①查询每个城市的部门个数

②在①结果上筛选满足条件的

select city,count(*)
from departments d 
inner join locations l
on d.location_id = l.location_id 
group by city
having count(*)>3;

查询员工个数大于3的部门名和员工个数，并按个数降序排序（添加排序）

select department_name,count(*) 员工个数
from departments d 
inner join employees e 
on d.department_id = e.department_id 
group by department_name
having count(*)>3 
order by 员工个数 desc ;

查询员工名、部门名、工种名，并按部门名排序

select last_name,department_name,job_title 
from departments d
inner join employees e on e.department_id = d.department_id 
inner join jobs j on j.job_id = e.job_id 
order by department_name asc;

2.非等值连接

查询员工的工资级别

select * from job_grades;
select salary,grade_level  
from employees e 
inner join job_grades j 
on e.salary between j.lowest_sal and j.highest_sal;

查询工资级别的个数大于二的个数，并且按工资级别降序排序

#1先查工资级别的个数
#2再查工资级别个数大于二的个数
#3最后按工资级别降序排序
select count(*) 工资级别个数,grade_level 
from employees e 
inner join job_grades j 
on e.salary between j.lowest_sal and j.highest_sal 
group by grade_level
having count(*)>3 
order by grade_level desc;

3.自连接

查询员工的名字、上级的名字

select * from employees;
select e.last_name,m.last_name 
from employees e
inner join employees m 
on e.manager_id = m.employee_id ; 

查询姓名中包含字符k的员工的名字、工资、上级的名字,并按工资升序排序

select e.last_name 员工名,e.salary 员工工资,m.last_name 上级名
from employees e
inner join employees m 
on e.manager_id = m.employee_id 
where e.last_name like '%k%'
order by 员工工资 asc; 

三：外连接

/*
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：1.外链接的所有结果为主表中的所有记录
如果从表中有和他匹配的则显示匹配的值，反之则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2.左外连接：left join左边的是主表
右外链接：right join 右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表一中有但表二中没有的+表二中有但表一中没有的

*/

引入：查询没有男朋友的女神名(查询男朋友不在男神表的女神名)

use girls;
show databases;
select * from beauty;
select * from boys;

左外连接

select b.name,bo.* 
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;
#

右外连接

select b.name,bo.* 
from boys bo right outer join beauty b
on b.boyfriend_id = bo.id
where bo.id is null;
#
#
select b.name,bo.* 
from boys bo left outer join beauty b 
on b.boyfriend_id = bo.id 
where bo.id is null;

案例：查询哪个部门没有员工

左外

use myemployees;
select d.*,e.employee_id 
from departments d 
left outer join employees e on d.department_id = e.department_id 
where e.employee_id is null;

右外

select d.department_name
from employees e right outer join departments d 
on d.department_id = e.department_id
where e. employee_id is null;

全外 （mysql不支持）

select b.*,bo.* 
from beauty b 
full outer join boys bo 
on b.boyfriend_id = bo.id ;

交叉连接 (笛卡尔乘积)

select b.*,bo.* 
from beauty b 
cross join boys bo ;

sql92和SQL99 PK

功能：SQL99支持的较多

可读性：SQL99实现连接条件和筛选条件的分离，可读性较高

练习

一、查询编号》3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

select b.*,bo.* 
from beauty b 
left outer join boys bo 
on b.boyfriend_id = bo.id
where b.id>3;

二、查询哪个城市没有部门

use myemployees;
select city,d.*
from departments d
right outer join locations l 
on d.location_id = l.location_id
where d.department_id is null;

三、查询部门名为sal或it的员工信息

select e.*,d.department_name ,d.location_id
from departments d
left join employees e 
on d.department_id = e.department_id 
where d.department_name in ('sal','it');