5、Oracle的多表查询

最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。

本文主要讲解以下几点：

• 使用等值和不等值连接在SELECT 语句中查询多个表中的数据。
• 使用自连接。
• 使用外连接查询不满足连接条件的数据。

1、笛卡尔积

select last_name, department_name from employees, departments

上述查询语句,因为没有写关联条件,查询得到的结果是两张表的笛卡尔积结果.

笛卡尔集会在下面条件下产生:

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

2、Oracle 连接

使用连接在多个表中查询数据。

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀

2.1、等值连接

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id,departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

2.2、多个连接条件与 AND 操作符

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id,departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id and employees.last_name='%T%';

2.3、区分重复的列名

• 使用表名前缀在多个表中区分相同的列。

• 在不同表具有相同列名的列可以用表的别名加以区分。

2.4、表的别名

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。

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

2.5、连接多个表

连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

2.6、非等值连接

查询：EMPLOYEES表中的列工资 应在JOB_GRADES表中的最高工资与最低工资之间

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;

3、内连接和外连接

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

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

3.1、外连接语法

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

右外连（联）接
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(+);

3.2、外连接

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

3.3、自连接

题目：查询employees表，返回“Xxx works for Xxx”

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

4、使用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)];

4.1、叉 集（了解）

• 使用CROSS JOIN 子句使连接的表产生叉集。
• 叉集和笛卡尔集是相同的。

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;

4.2、自然连接

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

返回的是，两个表中具有相同名字的列的“且、交集”，而非“或，并集”。即：比如employee类和department类都有department_id和manager_id，返回二者都相同的结果。

SELECT department_id, department_name,location_id, city
FROM   departments
NATURAL JOIN locations ;

4.3、使用 USING 子句创建连接

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

-- 会自动匹配表中相同的列，可多列匹配；
select last_name,department_name  
from employees
natural  join departments ; select  last_name,department_name 
from employees 
join departments using (department_id);

USING 子句

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

4.4、使用ON 子句创建连接（常用）

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

**ON 子句 **

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

4.5、使用 ON 子句创建多表连接

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;

5、内连接和外连接（2）

• 在SQL: 1999中，内连接只返回满足连接条件的数据
• 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。
• 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。
  下面连接查询的语句中的 outer 可以忽略，比如： left outer join <---> left join

5.1、左外连接

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

5.2、右外连接

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) ;

5.3、满外连接

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) ;