Oracle OCP笔记(17)多个表的连接查询

Oracle OCP笔记(17)多个表的连接查询

一.传统的Oracle连接语法
(1).自然连接
select regions.region_name, countries.country_name
  from regions, countries
 where regions.region_id = countries.region_id;


(2).外连接
-- 左外连接(同时返回左边表因不满足连接条件而被排除在外的行)
select last_name, department_name
  from departments, employees
 where departments.department_id = employees.department_id(+);


-- 右外连接(同时返回右边表因不满足连接条件而被排除在外的行)
select last_name, department_name
  from employees, departments
 where employees.department_id(+) = departments.department_id;


***传统的连接语法不支持全外连接


(3).交叉连接(笛卡尔乘积)
select * from regions, countries;


二.ANSI SQL:1999标准连接语法
SELECT table1.column, table2.column
  FROM table1 [NATURAL JOIN table2]|
              [JOIN table2 USING (column_name)]|
              [JOIN table2 ON (table1.column_name = table2.column_name)]|
              [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]|
              [CROSS JOIN table2]
 WHERE ...


(1).自然连接
-- 纯自然连接(NATURAL JOIN)，依据两个表中具有相同名称的列
select *
  from regions natural join countries 
 where country_id = 'US';


-- 自然连接(JOIN ... USING)
select *
  from location join countries using (country_id);


-- 自然连接(JOIN ... ON)
select *
  from departments d join employees e 
       on (e.employee_id = d.department_id);


-- 多路连接查询
select r.region_name, c.country_name, l.city, d.department_name
  from departments d
       join locations l on (l.location_id = d.location_id)
       join countries c on (c.country_id = l.country_id)
       join regions r on (r.region_id = c.region_id);


select r.region_name, c.country_name, l.city, d.department_name
  from departments d
       join locations l using (location_id)
       join countries c using (country_id)
       join regions r using (region_id);


(2).外连接
-- 左外连接(LEFT OUTER JOIN)(同时返回左边表因不满足连接条件而被排除在外的行)
select e.employee_id, e.department_id emp_dept_id, d.department_id dept_dept_id, d.department_name
  from departments d left outer join employees e
       on (d.department_id = e.department_id)
 where d.department_name like 'P%';


-- 右外连接(RIGHT OUTER JOIN)(同时返回右边表因不满足连接条件而被排除在外的行)
select e.employee_id, e.department_id emp_dept_id, d.department_id dept_dept_id, d.department_name
  from employees e right outer join departments d 
       on (e.department_id = d.department_id)
 where d.department_name like 'P%';


-- 全外连接(FULL OUTER JOIN)(左外连接和右外连接的组合结果)
select table1.column, table2.column
  from table1 full outer join table2
       on (table1.column = table2.column);


***传统的连接语法不支持全外连接


(3).交叉连接(笛卡尔乘积)(CROSS JOIN)
select table1.column, table2.column
  from table1 cross join table2;