1、外连接(左外连接,右外连接、全外连接):
指定了OUTER关键字的为外连接,外连接的结果表中不但包含满足连接条件的行,还包括相应表中所有的行。
---外连接
select count(*) from departments s;
select distinct s.department_id from employees s;
---统计一下那些部门不再雇员表中使用
select *
from departments t
where t.department_id not in
(select distinct s.department_id
from employees s
where department_id is not null)
--查询一下每个雇员的名称,邮箱等和部门名称(要统计所有的员工)
select *
from employees s
full join departments t
on s.department_id = t.department_id;
--Oracle独有外连接
--等值连接
select s.employee_id, s.first_name, t.department_name, t.department_id
from employees s, departments t
where s.department_id=t.department_id
--等值连接改造成Oracle独有外连接
---相当于左连接
select s.employee_id, s.first_name, t.department_name, t.department_id
from employees s, departments t
where s.department_id=t.department_id(+)
--相当于右连接
select s.employee_id, s.first_name, t.department_name, t.department_id
from employees s, departments t
where s.department_id(+)=t.department_id
2、左外连接(LEFT OUTER JOIN):
结果表中除了包括满足连接条件的行外,还包括左表的所有行。
select *
from employees s
left outer join departments t
on s.department_id = t.department_id;
3、右外连接(RIGHT OUTER JOIN):
结果表中除了包括满足连接条件的行外,还包括右表的所有行。
--右外连接:
select *
from employees s
right outer join departments t
on s.department_id = t.department_id;
4、完全外连接(FULL OUTERJOIN):
结果表中除了包括满足连接条件的行外,还包括两个表的所有行。
作业
–统计每个部门的平均工资,显示平均工资和部门工资
select avg(s.salary), t.department_name
from employees s
left outer join departments t
on s.employee_id = t.department_id
group by t.department_name
order by avg(s.salary)
select s.salary 部门工资 from employees s
left outer join departments t
on s.employee_id = t.department_id
–统计每个职位的最高工资,显示最高工资和职位名称
select max(s.salary) 最高工资, j.job_title
from employees s
left outer join jobs j
on s.job_id = j.job_id
group by j.job_title
order by max(s.salary)