【Oracle】第四单元 多表关联查询

等于链接

SELECT e.employee_id
      ,e.department_id
      ,d.department_name
      ,d.location_id
  FROM employees   e
      ,departments d
 WHERE e.department_id = d.department_id;

不等链接

包括＞，<，!=，between

SELECT e.last_name
      ,e.salary
      ,j.grade_level
  FROM employees  e
      ,job_grades j
 WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

外链接

包括左外连接，右外连接

SELECT e.last_name
      ,e.department_id
      ,d.department_name
  FROM employees   e
      ,departments d
 WHERE e.department_id = d.department_id(+);

SELECT e.last_name
      ,e.department_id
      ,d.department_id
      ,d.department_name
  FROM employees   e
      ,departments d
 WHERE e.department_id(+) = d.department_id;

自链接

是一种概念，某个table和自己本身链接，比如：table1给另一个“自己”起别名为table2

SELECT worker.last_name || ' works for ' || manager.last_name,
       worker.employee_id,
       worker.manager_id
  FROM employees worker, employees manager
 WHERE worker.manager_id = manager.employee_id;

交叉连接

相当于没有链接条件的多表关联查询，结果是个笛卡尔乘积，实际工作中很少用到

select last_name,department_name
 from employees
 cross join departments

自然链接

相当于Oracle的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段为“等于连接”条件

注：如果两个表中有相同的列名，但字段类型不一样，会引起错误

避免使用

select department_id, department_name, location_id, city
  from departments natural
  join locations

相当于

select department_id, department_name, d.location_id, city
  from departments d, locations l
 Where d.location_id = l.location_id;

Using子句

Using子句可开着是 自然连接 的一种补充功能，我们知道自然连接会让系统自动查找两张表中的所有列名相同的字段，并试图建立“等于连接”；但有的时候我们不期望这么做，而只是期望某个特定的字段作为“等于连接”的条件，这种情况下可以使用Using 子句来做限制

SELECT e.employee_id, e.last_name, d.location_id
  FROM employees e
  JOIN departments d
 USING (department_id);

内连接

相当于Oracle的“等于链接” ， 关键字：INNER JOIN

注：INNER JOIN 可简写为JOIN ,即省去INNER

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id
JOIN locations l ON d.location_id = l.location_id;

外连接

可细分为左外连接、右外连接、全外连接

左外连接

SELECT employee_id, city, department_name
  FROM employees e
  JOIN departments d
    ON d.department_id = e.department_id
  JOIN locations l
    ON d.location_id = l.location_id;

右外连接

SELECT e.last_name, e.department_id, d.department_name
  FROM employees e
 RIGHT OUTER JOIN departments d
    ON (e.department_id = d.department_id);

全外连接

SELECT e.last_name, e.department_id, d.department_name
  FROM employees e
  FULL OUTER JOIN departments d
    ON (e.department_id = d.department_id);