Oracle连接全接触

CREATE TABLE SCOTT.EMPLOYEES
(
   employee_id varchar2(5) not null,
   last_name varchar2(20),
   department_id varchar2(5)
)

INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('100','king','90');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('101','kochhar','90');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('102','Tom','90');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('103','kochhar','90');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('202','Fay','20');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('203','Jim','20');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('204','Green','50');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('205','Higgins','110');
INSERT INTO SCOTT.EMPLOYEES(EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID)
       VALUES('206','Gietz','110');
      
CREATE TABLE SCOTT.DEPARTMENTS
(
       department_id varchar2(5) not null,
       department_name varchar2(20),
       location_id varchar2(50)
)

insert into scott.departments(department_id,department_name,location_id) values('20','Marketing','1800');
insert into scott.departments(department_id,department_name,location_id) values('40','Shopping','2000');
insert into scott.departments(department_id,department_name,location_id) values('90','IT','1400');
insert into scott.departments(department_id,department_name,location_id) values('110','Sales','2500');

--- 笛卡尔乘积 等于 交叉连接
--笛卡尔乘积 employees * department(Oracle 8i,Oracle 9i)
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from scott.employees  ,scott.departments   --交换位置连接结果不表

--交叉连接  employees * department(Oracle 9i)
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from scott.employees 
 cross join scott.departments   --交换位置连接结果不表

--对两张表中都有的数据做连接
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from scott.employees  ,scott.departments   --交换位置连接结果不表
where   employees.department_id = departments.department_id --交换位置结果不变
order by employees.employee_id

--自然连接
--基于两个表之间有相同名字的所有列
--它从两个表中选择在所有的匹配列中有相等值的行
--注意：不能使用限定词
select  *
from  scott.employees
 natural join   scott.departments --交换位置连接结果不表
order by employee_id

--对两张表中都有的数据做连接
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from  scott.employees
  JOIN    scott.departments  --交换位置连接结果不表
  ON   employees.department_id = departments.department_id --交换位置结果不变
order by employees.employee_id

 

--对两张表中都有的数据做连接 Employees左连接
--用Employees表的所有数据连接Department表(可能为空)
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from scott.employees  ,scott.departments   --交换位置连接结果不表
where   employees.department_id = departments.department_id(+) --交换位置结果不变
order by employees.employee_id

select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from   scott.employees
    LEFT OUTER JOIN   scott.departments --交换位置连接结果变化
    ON   employees.department_id = departments.department_id --交换位置结果不变
order by employees.employee_id

--对两张表中都有的数据做连接 Employees右连接
--用Department表的所有数据连接Employees表(可能为空)
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from scott.employees, scott.departments    --交换位置连接结果不表
where   employees.department_id(+) = departments.department_id --交换位置结果不变
order by employees.employee_id

select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from   scott.employees
    RIGHT OUTER JOIN   scott.departments --交换位置连接结果变化
    ON   employees.department_id = departments.department_id --交换位置结果不变
order by employees.employee_id

--从中可以看出Employees左连接等于Department的右连接

--全连接
select  employees.EMPLOYEE_ID,employees.LAST_NAME,employees.DEPARTMENT_ID,
        departments.department_id,departments.department_name,departments.location_id
from   scott.employees
    FULL OUTER JOIN    scott.departments--交换位置连接结果变化
    ON   employees.department_id = departments.department_id --交换位置结果不变
order by employees.employee_id