多表连接查询

--连接类型：

--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 ;