Oracle（五）多表查询

笛卡尔集的错误（没有连接条件）

select employees.employee_id,employees.department_id,departments.department_name
from employees,departments;

select employees.employee_id,employees.department_id,departments.department_name
from employees,departments
where employees.department_id = departments.department_id;

假设有n个表作为查询，至少连接条件得有n - 1;

select employee_id,last_name,departments.department_id,department_name,departments.location_id,city
from employees,departments,locations
where employees.department_id = departments.department_id and departments.location_id = locations.location_id;

内连接

• 等值连接 vs 非等值连接
• 自连接 vs 非自连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行；

等值连接（表可以起别名，可以将别名作为连接条件使用）

select employee_id,last_name,d.department_id,department_name,d.location_id,city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id;

非等值连接

select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary >= j.lowest_sal and e.salary <= j.highest_sal;

自连接

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

外连接

外连接(除了查询到满足条件的行之外，还查询出不满足条件的行，两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+)；

使用外连接可以查询不满足连接条件的数据，外连接的符号是 (+):

--右外连接
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

--左外连接
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

左外连接：除了查询到满足条件的行之外，还查询出左表中不满足条件的行

-- 比如有的员工没有部门
select employee_id,e.department_id,department_name
 from employees e,departments d
 where e.department_id = d.department_id(+);

右外连接：除了查询到满足条件的行之外，还查询出右表中不满足条件的行

--有的部门没有员工
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id(+) = d.department_id;

像这种情况：

如下的操作时错误的。

select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id(+) = d.department_id(+);

使用SQL: 1999 语法连接

SELECT	table1.column, table2.column
FROM	table1
[CROSS JOIN table2] |
[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 子句使连接的表产生叉集。
• 叉集和笛卡尔集是相同的。

自然连接

• NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
• 在表中查询满足等值条件的数据。
• 如果只是列名相同而数据类型不同，则会产生错误。

--使用在内连接上
--natural join：自动将两个表中所有相同的列名作为连接条件。（有局限性）
select employee_id,last_name,department_id,department_name
from employees natural join departments;

--与下述操作相同
select employee_id,last_name,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id and e.manager_id = d.manager_id;

使用 USING 子句创建连接

• 在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。
• 使用 USING 可以在有多个列满足条件时进行选择。
• 不要给选中的列中加上表名前缀或别名。
• JOIN 和 USING 子句经常同时使用。

--join ... using：要求两个表的作为连接条件的列的列名一样。（有局限性）
select employee_id,last_name,department_id,department_name
from employees join departments 
using(department_id);

使用ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的，可以使用 ON 子句指定额外的连接条件，这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。

--join ... on ...具有通用性，需要掌握。
select employee_id,last_name,e.department_id,department_name
from employees e join departments d
on e.department_id = d.department_id;

select employee_id,department_name,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = d.location_id;

内连接和外连接

在SQL: 1999中，内连接只返回满足连接条件的数据
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。

左外连接

select employee_id,last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id = d.department_id;

右外连接

select employee_id,last_name,e.department_id,department_name
from employees e right outer join departments d
on e.department_id = d.department_id;

满外连接

select employee_id,last_name,e.department_id,department_name
from employees e full outer join departments d
on e.department_id = d.department_id;