Oracle DB 使用连接显示多个表中的数据

￡神魔★判官ぃ 2022-04-23 09:04 160阅读 0赞

• 编写SELECT语句，以使用等值联接和非等值联接访问 多个表中的数据

• 使用自联接将表联接到自身

• 使用OUTER联接查看通常不满足联接条件的数据

• 生成两个或多个表中所有行的笛卡尔积

• JOIN的类型及其语法

• 自然联接：

– USING子句

– ON子句

• 自联接

• 非等值联接

• OUTER联接：

– LEFT OUTER联接

– RIGHT OUTER联接

– FULL OUTER联接

• 笛卡尔积

– 交叉联接

*  **获取多个表中的数据**

有时需要使用多个表中的数据。如两个独立表中的数据：

• 雇员ID 在EMPLOYEES表中。

• 部门ID 在EMPLOYEES和DEPARTMENTS两个表中。

• 部门名称在DEPARTMENTS表中。

要生成该报表，需要将EMPLOYEES表和DEPARTMENTS表链接起来，然后访问这两个表 中的数据。

*  **联接类型**

符合SQL:1999 标准的联接包括：

• 自然联接：

– NATURAL JOIN子句

– USING子句

– ON子句

• OUTER联接：

– LEFT OUTER JOIN

– RIGHT OUTER JOIN

– FULL OUTER JOIN

• 交叉联接

要联接各个表，可以使用符合SQL:1999 标准的联接语法。

**附注**

• 在Oracle9i之前的发行版中，该联接语法与美国国家标准协会(ANSI) 的标准不同。

与以前发行版中的Oracle 专用联接语法相比，符合SQL:1999 的联接语法没有任何 性能优势。

*  **使用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\];

在该语法中：

• table1.column表示从中检索数据的表和列

• NATURAL JOIN 根据相同的列名联接两个表

• JOIN table2 USING column\_name 根据列名执行等值联接

• JOIN table2 ON table1.column\_name = table2.column\_name根据 ON子句中的条件执行等值联接

• LEFT/RIGHT/FULL OUTER用于执行OUTER联接

• CROSS JOIN用于返回两个表的笛卡尔积

*  **限定不确定的列名**

• 使用表前缀可以限定多个表中的列名。

• 使用表前缀可以提高性能。

• 可以使用表别名来代替完整表名前缀。

• 表别名是表的短名称：

– 使SQL 代码变得更短，因而占用更少的内存

• 使用列别名可区分具有相同名称但位于不同表中的列。

联接两个或更多表时，需要使用表名来限定列的名称，以避免混淆。如果不使用表前缀， 则SELECT列表中的DEPARTMENT\_ID列可能来自DEPARTMENTS表，也可能来自 EMPLOYEES表。因此需要添加表前缀来执行查询。如果两个表中没有相同的列名，则 无需限定列。但是，使用表前缀可以提高性能，因为这等于告知Oracle Server 查找这些列 的确切位置。

但是，使用表名限定列名可能非常耗时，特别是当表名较长时。可改为使用表别名。就像 列别名是列的另一个名称一样，表别名也是表的另一个名称。表别名有助于使SQL 代码 变得更短，因而占用更少的内存。

先指定表全名，然后是一个空格，再后面是表别名。例如，EMPLOYEES表的别名可以 是e，而DEPARTMENTS表的别名可以是d。

**准则**

• 表别名的长度最多为 **30** 个字符，但越短越好。

• 如果在FROM子句中使用了某个特定表名的表别名，则必须在整个SELECT语句中 使用该表别名代替该表名。

• 表别名应是有意义的名称。

• 表别名仅对当前的SELECT语句有效。

*  **创建自然联接**

• NATURAL JOIN子句以 **两个表中具有相同名称的所有列为基础** 。

• 它从两个表中选择在所有相匹配列中具有相同值的 那些行。

• **如果名称相同的列具有不同的数据类型，则返回一个错误。**

可以根据两个表中具有 **相匹配的数据类型和名称的那些列**，对表执行自动联接。使用 NATURAL JOIN关键字可以完成此操作。

注：只能对两个表中具有 相同名称和数据类型的那些列执行联接。如果列的名称相同但 数据类型不同，那么NATURAL JOIN语法将导致产生一个错误。

*  **使用自然联接检索记录**

hr@TEST0924> DESC departments

Name Null? Type

\----------------------------------------------------- -------- ------------------------------------

DEPARTMENT\_ID NOT NULL NUMBER(4)

DEPARTMENT\_NAME NOT NULL VARCHAR2(30)

MANAGER\_ID NUMBER(6)

LOCATION\_ID NUMBER(4)

hr@TEST0924> DESC locations

Name Null? Type

\----------------------------------------------------- -------- ------------------------------------

LOCATION\_ID NOT NULL NUMBER(4)

STREET\_ADDRESS VARCHAR2(40)

POSTAL\_CODE VARCHAR2(12)

CITY NOT NULL VARCHAR2(30)

STATE\_PROVINCE VARCHAR2(25)

COUNTRY\_ID CHAR(2)

*hr@TEST0924> SELECT department\_id,department\_name, location\_id, city FROM departments NATURAL JOIN locations ;*

*DEPARTMENT\_ID DEPARTMENT\_NAME LOCATION\_ID CITY*

*------------- ------------------------------ ----------- ------------------------------*

*60 IT 1400 Southlake*

*50 Shipping 1500 South San Francisco*

*10 Administration 1700 Seattle*

*...*

*28 rows selected.*

在示例中，通过LOCATION\_ID列将LOCATIONS表和DEPARTMENT表联接 起来，LOCATION\_ID列是两个表中具有相同名称的唯一列。如果存在其它通用列，联接

也会使用所有这些列。

**使用WHERE子句的自然联接**

可以使用WHERE子句对自然联接施加其它限制。以下示例将输出行限制为部门ID 等于 20 或50 的那些行：

*hr@TEST0924> SELECT department\_id, department\_name, location\_id, city FROM departments NATURAL JOIN locations WHERE department\_id IN (20, 50);*

*DEPARTMENT\_ID DEPARTMENT\_NAME LOCATION\_ID CITY*

*------------- ------------------------------ ----------- ------------------------------*

*20 Marketing 1800 Toronto*

*50 Shipping 1500 South San Francisco*

*  **使用USING子句创建联接**

• 如果多个列具有相同的名称，但数据类型不匹配， 请使用USING子句指定等值联接的列。

• 当有多个列相匹配时，使用USING子句可仅与一列 相匹配。

• NATURAL JOIN和USING语句是互相排斥的。

自然联接使用具有 匹配的名称和数据类型的所有列来联接表。可以使用USING子句仅 指定等值联接应使用的那些列。

*  **联接列名**

要确定雇员的部门名称，应将EMPLOYEES表中DEPARTMENT\_ID列的值与 DEPARTMENTS表中DEPARTMENT\_ID列的值进行比较。EMPLOYEES表和

DEPARTMENTS表之间的关系就是等值联接关系，即这两个表中DEPARTMENT\_ID列的 值必须相等。通常，这种类型的联接涉及到主键和外键补码。

注：等值联接也称为简单联接或内部联接。

*  **使用USING子句检索记录**

*hr@TEST0924> SELECT employee\_id, last\_name, location\_id, department\_id FROM employees JOIN departments USING (department\_id) ;*

*EMPLOYEE\_ID LAST\_NAME LOCATION\_ID DEPARTMENT\_ID*

*----------- ------------------------- ----------- -------------*

*200 Whalen 1700 10*

*201 Hartstein 1800 20*

*...*

*106 rows selected.*

在示例中，由于EMPLOYEES表和DEPARTMENTS表中的DEPARTMENT\_ID 列已联接起来，因此会显示雇员所在部门的LOCATION\_ID。

*  **在USING子句中使用表别名**

hr@TEST0924> SELECT l.city, d.department\_name FROM locations l JOIN departments d USING (location\_id) WHERE d.location\_id = 1400;

SELECT l.city, d.department\_name FROM locations l JOIN departments d USING (location\_id) WHERE d.location\_id = 1400

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier

**• 不要对USING子句中使用的列加以限定。**

**• 如果在SQL 语句的另一个位置使用了同一列，则不要对其设置别名。**

使用USING子句进行联接时， 不能对USING子句自身中使用的列加以限定。此外，如果 在SQL 语句的任何位置使用了该列，则不能对其设置别名。例如，在示例提及的查询 中，不能对WHERE子句中的location\_id列设置别名，因为在USING子句中已使用 该列。

USING子句中引用的那些列不能在SQL 语句的任何位置使用限定词（表名或别名）。

例如，下面的语句是有效的：

*hr@TEST0924> SELECT l.city, d.department\_name FROM locations l JOIN departments d USING (location\_id) WHERE location\_id = 1400;*

*CITY DEPARTMENT\_NAME*

*------------------------------ ------------------------------*

*Southlake IT*

这两个表中通用但未在USING子句中使用的列必须以表别名为前缀，否则会出现 “column ambiguously defined（定义的列含糊不清）”错误。

在下面的语句中，manager\_id既在employees表中又在departments表中，如果 manager\_id不以表别名为前缀，则会收到“column ambiguously defined（定义的列含糊 不清）”错误。

下面的语句是有效的：

*hr@TEST0924> SELECT first\_name, d.department\_name, d.manager\_id FROM employees e JOIN departments d USING (department\_id) WHERE department\_id = 50;*

*FIRST\_NAME DEPARTMENT\_NAME MANAGER\_ID*

*-------------------- ------------------------------ ----------*

*Donald Shipping 121*

*Douglas Shipping 121*

*...*

*45 rows selected.*

*  **使用ON子句创建联接**

• 自然联接的基本联接条件是对具有相同名称的所有列 进行等值联接。

• 使用ON子句可指定任意条件或指定要联接的列。

• 联接条件独立于其它搜索条件。

• 使用ON子句可使代码易于理解。

使用ON子句可指定联接条件。这样，便可以在WHERE子句中指定独立于任何搜索条件 或过滤条件的联接条件。

*  **使用ON子句检索记录**

*hr@TEST0924> SELECT e.employee\_id, e.last\_name, e.department\_id, d.department\_id, d.location\_id*

*2 FROM employees e JOIN departments d ON (e.department\_id = d.department\_id);*

*EMPLOYEE\_ID LAST\_NAME DEPARTMENT\_ID DEPARTMENT\_ID LOCATION\_ID*

*----------- ------------------------- ------------- ------------- -----------*

*200 Whalen 10 10 1700*

*201 Hartstein 20 20 1800*

*...*

*106 rows selected.*

在此示例中，EMPLOYEES表和DEPARTMENTS表中的DEPARTMENT\_ID列已使用ON 子句联接起来。只要EMPLOYEES表中的部门ID 等于DEPARTMENTS表中的部门ID， 就返回相应的行。要限定相匹配的column\_names，表别名是必需的。 还可以使用ON子句来联接名称不相同的列。示例所示的联接列

(e.department\_id = d.department\_id)两边的圆括号是可选的。因此，即便是 ON e.department\_id = d.department\_id效果也一样。

*  **使用ON子句创建三向联接**

*hr@TEST0924> SELECT employee\_id, city, department\_name FROM employees e JOIN departments d*

*2 ON d.department\_id = e.department\_id JOIN locations l ON d.location\_id = l.location\_id;*

*EMPLOYEE\_ID CITY DEPARTMENT\_NAME*

*----------- ------------------------------ ------------------------------*

*100 Seattle Executive*

*101 Seattle Executive*

*102 Seattle Executive*

*...*

*106 rows selected.*

三向联接是指三个表的联接。在符合SQL:1999 的语法中，从左至右执行联接。这样， 要执行的第一个联接是EMPLOYEES JOIN DEPARTMENTS。第一个联接条件可以引用 EMPLOYEES和DEPARTMENTS中的列，但不能引用LOCATIONS中的列。第二个联接 条件可以引用全部三个表中的列。

注：示例也可以使用USING子句实现：

*hr@TEST0924> SELECT e.employee\_id, l.city, d.department\_name FROM employees e JOIN departments d USING (department\_id) JOIN locations l USING (location\_id)*

*2 ;*

*EMPLOYEE\_ID CITY DEPARTMENT\_NAME*

*----------- ------------------------------ ------------------------------*

*100 Seattle Executive*

*101 Seattle Executive*

*102 Seattle Executive*

*...*

*106 rows selected.*

*  **对联接应用附加条件**

使用AND子句或WHERE子句可应用附加条件：

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

*AND e.manager\_id = 149 ;*

或

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

*WHERE e.manager\_id = 149 ;*

可以对联接应用附加条件。

所示示例对EMPLOYEES和DEPARTMENTS表执行联接，另外，还仅显示经理ID 为149 的雇员。要对ON子句添加附加条件，可以添加AND子句。或者，可以使用WHERE子句 应用附加条件。

*  **将表联接到自身**

有时需要将表联接到自身。要查找每位雇员的经理的姓名，需要将EMPLOYEES表联接到 自身，即执行自联接。例如，要查找Lorentz 的经理的姓名，需要执行以下操作：

• 在EMPLOYEES表中通过搜索LAST\_NAME列来查找Lorentz。

• 通过搜索MANAGER\_ID列查找Lorentz 的经理编号。Lorentz 的经理编号为103。

• 通过搜索LAST\_NAME列查找EMPLOYEE\_ID为103 的经理的姓名。Hunold的雇员 编号为103，因此Hunold是Lorentz 的经理。

在这一过程中，对表进行了两次搜索。第一次是在表中LAST\_NAME列中查找Lorentz， 得知其MANAGER\_ID值为103。第二次是搜索EMPLOYEE\_ID列以查找103，然后在 LAST\_NAME列中找到了Hunold。

*  **使用ON子句进行自联接**

*SELECT worker.last\_name emp, manager.last\_name mgr FROM employees worker JOIN employees manager*

*ON (worker.manager\_id = manager.employee\_id);*

ON子句还可以用于联接 同一表内或者不同表中具有不同名称的列。

所示示例为EMPLOYEES表基于EMPLOYEE\_ID和MANAGER\_ID列进行自联接。

注：示例所示的联接列(e.manager\_id = m.employee\_id)两边的圆括 号是可选的。因此，即便是ON e.manager\_id = m.employee\_id效果也一样。

*  **非等值联接**

非等值联接是一个包含非等号运算符的联接条件。

EMPLOYEES表和JOB\_GRADES表之间的关系就是一个非等值联接的示例。EMPLOYEES 表中的SALARY列的范围介于JOB\_GRADES表的LOWEST\_SAL和HIGHEST\_SAL列中 的值之间。因此，可以根据每位雇员的薪金划分其等级。通过使用等号(=) 以外的运算符 可以实现这一关系。

*  **使用非等值联接检索记录**

*SELECT e.last\_name, e.salary, j.grade\_level FROM employees e JOIN job\_grades j ON e.salary BETWEEN j.lowest\_sal AND j.highest\_sal;*

示例中创建了一个非等值联接来评估雇员的薪金等级。薪金必须介于任何一对最低 薪金和最高薪金之间。

值得注意的是，在执行这一查询时，所有雇员只能出现一次。雇员不能在列表中重复出现。

这有以下两个原因：

• JOB\_GRADES表中的任何一行都不包含重叠的等级。也就是说，每位雇员的薪金值 只能介于薪金等级表中某一行的最低薪金值和最高薪金值之间。

• 所有雇员的薪金都在职务等级表提供的限额之内。也就是说，任何雇员的薪金都不 低于LOWEST\_SAL列中的最低值，也不高于HIGHEST\_SAL列中的最高值。

注：可以使用其它条件，如<=和>=，但最简单的方法是使用BETWEEN。请记住，在使 用BETWEEN条件时，应先指定最低值，后指定最高值。Oracle Server 将BETWEEN条件 解释为一对AND条件。因此，使用BETWEEN没有性能优势，只是为了简化逻辑才使用它。

示例中指定了表别名，这是考虑到性能原因，而不是因为可能出现的混淆。

*  **使用OUTER联接返回没有直接匹配的记录**

如果某一行不满足联接条件，则查询结果中就不会出现该行。

针对EMPLOYEES和DEPARTMENTS表使用简单等值联接来返回右侧的 结果。结果集不包含以下内容：

• 部门ID 190，因为EMPLOYEES表中不存在具有该部门ID 的雇员

• 姓氏为Grant 的雇员，因为没有为该雇员分配部门ID

要返回没有任何雇员部门记录，或者返回没有分配部门的雇员，您可以使用OUTER联接。

*  **INNER联接与OUTER联接**

• 在SQL:1999 中，如果两个表的联接只返回相匹配的行， 则称该联接为INNER联接。

• 如果两个表之间的联接不仅返回INNER联接的结果， 还返回左（或右）表中不匹配的行，则称该联接为左 （或右）OUTER联接。

• 如果两个表之间的联接不仅返回INNER联接的结果， 还返回左和右联接的结果，则称该联接为完全OUTER 联接。

使用NATURAL JOIN、USING或ON子句对表进行联接时会生成INNER联接。输出中不 会显示任何不匹配的行。要返回不匹配的行，可以使用OUTER联接。OUTER联接将返回 满足联接条件的所有行，还会返回一个表在另一表中没有满足联接条件的对应行的部分或 全部行。

有三种OUTER联接类型：

• LEFT OUTER

• RIGHT OUTER

• FULL OUTER

*  **LEFT OUTER JOIN**

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

此查询将检索EMPLOYEES表（它是左表）中的所有行，即使DEPARTMENTS表中没有 匹配项也是如此。

*  **RIGHT OUTER JOIN**

*SELECT e.last\_name, d.department\_id, d.department\_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department\_id = d.department\_id);*

此查询将检索DEPARTMENTS表（它是右表）中的所有行，即使EMPLOYEES表中没有 匹配项也是如此。

*  **FULL OUTER JOIN**

SELECT e.last\_name, d.department\_id, d.department\_name FROM employees e FULL OUTER JOIN departments d ON (e.department\_id = d.department\_id) ;

此查询将检索EMPLOYEES表中的所有行，即使DEPARTMENTS表中没有匹配项也是如此。 它还检索DEPARTMENTS表中的所有行，即使EMPLOYEES表中没有匹配项也是如此。

*  **笛卡尔积**

• 出现以下情况时将形成笛卡尔积：

– 联接条件被忽略

– 联接条件无效

– 第一个表中的所有行被联接到第二个表中的所有行

• 如果要避免生成笛卡尔积，请始终包括有效的联接条件。

当一个联接条件无效或被完全忽略时，就会生成笛卡尔积。此时会显示行的所有组合。 第一个表中的所有行会被联接到第二个表中的所有行。

笛卡尔积往往会生成大量的行，这种结果几乎没有任何用处。因此，应始终包括有效的 联接条件，除非有特定需求需要组合所有表中的所有行。

如果某些测试需要生成大量的行来模拟合理的数据量，则笛卡尔积非常有用。

*  **生成笛卡尔积**

如果某个联接条件被忽略，则会生成笛卡尔积。 由于没有指定联接条件，EMPLOYEES表中 的所有行（20 行）与DEPARTMENTS表中的所有行（8 行）联接在一起，因此在输出中 生成了160 行。

*  **创建交叉联接**

• CROSS JOIN子句可生成两个表的叉积。

• 这也称为两个表间的笛卡尔积。

*SELECT last\_name, department\_name FROM employees CROSS JOIN departments ;*

示例生成EMPLOYEES表和DEPARTMENTS表的笛卡尔积。

CROSS JOIN方法非常有用，可以应用于许多情况。例如，要按月按办公室返回总人工 成本，即使X 月没有人工成本，也可以执行所有月的表与办公室表的交叉联接。

如果要创建笛卡尔积，最佳做法是在SELECT中显式说明CROSS JOIN。因此，可以 非常清楚表明要生成笛卡尔积，而不是缺少联接的结果。

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485

Oracle DB 使用连接显示多个表中的数据

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle中的表连接

相关 NHibernate4使用Oracle.ManagedDataAccess.dll连接oracle及配置多个数据库连接

相关 C#WPF中的两表多数据连接

相关 Oracle左连接中多个约束条件

相关在Laravel中配置使用多个Datebase的连接，DB

相关 Oracle左连接中多个约束条件

相关 Oracle DB 使用连接显示多个表中的数据

相关 Oracle-查询-多表连接查询

相关 Redis 配置连接池，redisTemplate 操作多个db数据库，切换多个db，解决JedisConnectionFactory的设置连接方法过时问题。

相关 Oracle多表连接