Oracle 多表查询

1、多表连接查询

在执行多表查询时，若未指定连接条件，则结果返回是个笛卡尔乘积，显然大多数情况下，笛卡尔乘积不是想要的结果，为了避免笛卡尔乘积，一般要在Where子句中提供连接条件，对于连接，通常又包括多种类型： 不同的数据库厂商对连接类型有不同的定义，Oracle定义的连接类型：

1.等于连接

  1. SELECT table1.column, table2.column
  2. FROM table1, table2
  3. WHERE table1.column1 = table2.column2;
  1. SELECT employees.employee_id, employees.last_name,
  2. employees.department_id,
  3. departments.department_id,departments.location_id
  4. FROM employees, departments
  5. WHERE employees.department_id = departments.department_id;

image.png

2.不等连接

  1. SELECT table1.column, table2.column
  2. FROM table1, table2
  3. WHERE table1.column1 > table2.column2;
  1. SELECT e.last_name, e.salary, j.grade_level
  2. FROM employees e, job_grades j
  3. WHERE e.salary
  4. BETWEEN j.lowest_sal AND j.highest_sal;

3.外连接（可细分为左外连接、右外连接）

  1. SELECT table1.column, table2.column
  2. FROM table1, table2
  3. WHERE table1.column(+) = table2.column; 
  5. SELECT table1.column, table2.column
  6. FROM table1, table2
  7. WHERE table1.column = table2.column (+);
  1. SELECT e.last_name, e.department_id, d.department_name
  2. FROM employees e, departments d
  3. WHERE e.department_id(+) = d.department_id ;

image.png

4.自连接

  1. SELECT table1.column, table2.column
  2. FROM table1, table1 table2
  3. WHERE table1.column1 = table2.column2;
  1. SELECT worker.last_name || ' works for ' || manager.last_name
  2. FROM employees worker, employees manager
  3. WHERE worker.manager_id = manager.employee_id ;

image.png

2、多表连接查询

1.交叉连接

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

  1. SELECT last_name, department_name
  2. FROM employees
  3. CROSS JOIN departments ;

image.png

2.自然连接

相当于Oracle的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段作为 “等于连接”条件；（注意如果两个表中有相同的列名，但字段类型不一样，这会引发一个错误）
image.png

  1. SELECT department_id, department_name,
  2. location_id, city
  3. FROM departments
  4. NATURAL JOIN locations ;

相当于

  1. SELECT department_id, department_name,
  2. location_id, city
  3. FROM departments, locations
  4. Where departments.location_id = locations.location_id;

image.png

3.Using 子句

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

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

image.png

4.内连接

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

  1. SELECT employee_id, city, department_name
  2. FROM employees e
  3. INNER JOIN departments d ON d.department_id = e.department_id
  4. INNER JOIN locations l ON d.location_id = l.location_id;

image.png
INNER JOIN 可简写为JOIN，即省去INNER

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

image.png

5.外连接

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

A、左外连接

  1. SELECT e.last_name, e.department_id, d.department_name
  2. FROM employees e
  3. LEFT OUTER JOIN departments d
  4. ON (e.department_id = d.department_id);

image.pngimage.png

B、右外连接

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

image.pngimage.png

C、全外连接

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

image.png
image.png