--连接类型:
--1、内连接 inner join:
--只返回两个表中所有满足连接条件的行
--2、外连接outer join:
--除了返回两个表中所有满足连接条件的行之外,还返回
--某个表中不满足连接条件的行
--3、交叉连接
--产生笛卡尔积的连接
--如果连接条件使用等号=,就可以叫做相等连接
--1、使用natural join关键字进行内连接
--这种语法会自动地从两个表中挑选所有同名同类型的列作为连接列
--进行相等连接
--查询每个部门的部门编号、名称、所在位置编号以及所在城市
--名称
select department_id,department_name,
location_id,city
from departments
natural join locations;
--缺点:
--1、不能自己挑选连接列
--2、代码不直观,不能看出连接条件是什么
--2、为了改进以上缺陷,可以使用using子句来做连接
--语法:using(列名),含义:使用指定的两个表中的同名列作为
--连接列进行相等连接
--查询每个部门的部门编号、名称、所在位置编号以及所在城市
--名称
select department_id,department_name,
location_id,city
from departments
join locations
using(location_id);
--使用using做连接,也有一个语法限制:
--using用到的连接列,在select语句的任何地方(包括using自己),
--都不允许加表别名来限定它
--ORA-01748: 此处只允许简单的列名
select department_id,department_name,
location_id,city
from departments d
join locations l
using(d.location_id);
--ORA-25154: USING 子句的列部分不能有限定词
select department_id,department_name,
location_id,city
from departments d
join locations l
using(location_id)
where l.location_id > 1700;
--如果定义了表别名,那么一般都在列名前面加表别名进行限定
select d.department_id,d.department_name,
location_id,l.city
from departments d
join locations l
using(location_id);
--3、使用on子句指定连接条件,比using更加灵活、方便
--查询雇员的编号、姓名、所在部门号、以及部门的位置编号
selecte.employee_id,e.last_name,e.department_id,
d.department_id,d.location_id
from employees e
join departments d
on (e.department_id =d.department_id )
where d.location_id =1700;
--实际中写连接查询,老式的写法还是最多的。
--它是将所有做连接的表名都写在from子句中,然后在
--where中写连接条件
selecte.employee_id,e.last_name,e.department_id,
d.department_id,d.location_id
from employees e, departments d
where e.department_id =d.department_id
and d.location_id = 1700;
--老式的写法一个弊病就是:它把连接条件和过滤条件
--混写在了一起,代码可读性较差。on子句就解决了这个问题
--3表连接查询
--写多个join on子句
--查询雇员的编号、所在部门号、以及部门所在城市名称
selecte.employee_id,d.department_id,l.city
from employees e
join departments d
on(e.department_id = d.department_id)
join locations l
on(l.location_id = d.location_id);
--自连接查询self join:
--一张表连接到它自己做连接查询。
--技巧:给一张表分别取两个不同的别名,看成是两张表,
--然后进行连接
--查询每个雇员的姓名及其对应的经理的名字
select worker.last_name,manager.last_name
from employees worker
join employees manager
on (worker.manager_id =manager.employee_id);
--使用不等连接:连接条件不使用等号。很少见
create table job_grades(
grade_level varchar2(1),
lowest_sal number,
highest_sal number
);
insert into job_grades
values('A',1000,2999);
insert into job_grades
values('B',3000,5999);
insert into job_grades
values('C',6000,7999);
--查询雇员的姓名、薪水以及工资级别
select e.last_name,e.salary,j.grade_level
from employees e
join job_grades j
on (e.salary between j.lowest_sal andj.highest_sal);
--外连接查询
--3种:
--left join:左外连接
--right join :右外连接
--full join:全外连接
--1)left join:左外连接
--除了返回两个表中所有满足连接条件的行之外,还返回左表中
--所有不满足连接条件的行。所谓左表,就是写在left关键字
--左边的表
--查询雇员的姓名、部门编号以及部门名称,要求没有部门的雇员
--也要显示
select e.last_name,e.department_id,
d.department_name
from employees e
left join departments d
on (e.department_id =d.department_id);
--2)right join:右外连接
--除了返回两个表中所有满足连接条件的行之外,还返回右表中
--所有不满足连接条件的行。所谓右表,就是写在rightjoin关键字
--右边的表
--查询雇员的姓名、部门编号以及部门名称,要求没有雇员的部门
--也要显示
select e.last_name,e.department_id,
d.department_name
from employees e
right join departments d
on (e.department_id =d.department_id);
--3)full join:全外连接
--除了返回两个表中所有满足连接条件的行之外,还返回两个表中
--所有不满足连接条件的行。
--查询雇员的姓名、部门编号以及部门名称,要求没有雇员的部门
--以及没有部门的雇员也要显示
select e.last_name,e.department_id,
d.department_name
from employees e
full join departments d
on (e.department_id =d.department_id);
--连接查询时,应该避免产生笛卡尔积:
--当一个连接查询没有写连接条件或者连接条件非法时,其产生的结果集的行数
--就等于第一个表的总行数乘上第二个表的总行数,这个
--结果集叫做笛卡尔积。
select last_name,department_name
from employees, departments ;
--连接条件非法
select last_name,department_name
from employees
join departments
on (1=1);
--如果想专门产生笛卡尔积,可以使用cross join
select last_name,department_name
from employees
cross join departments ;