ORACLE学习笔记之表连接

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了ORACLE学习笔记之表连接，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
sql 中使用 JOIN 关键字来使用表连接。表连接有多种不同的类型，被主流数据库系统支持的有交叉连接（CROSS JOIN） 、内连接（INNER JOIN） 、外连接（OUTTER JOIN） ，另外在有的数据库系统中还支持联合连接（UNION JOIN） 。

内连接（INNER JOIN）
内连接组合两张表，并且基于两张表中的关联关系来连接它们。使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。内连接的语法如下：
INNER JOIN table_nameON condition
其中 table_name 为被关联的表名，condition 则为进行连接时的条件。
下面的 sql 语句检索所有的客户姓名为 MIKE 的客户的订单号以及订单价格：
 SELECTFNumber,FPrice
 FROMT_OrderINNER JOINT_Customer
 ONFCustomerId= T_Customer.FId
 WHERET_Customer.FName='TOM'
执行完毕我们就能在输出结果中看到下面的执行结果：
 FNumber FPrice
 K001 100.00
 K002 200.00
 T003 300.00
 T001 300.00
在这个 sql 语句中，首先列出了组成结果集所需要的列名，而后则是在 FROM 关键字后指定需要的表，在 INNER JOIN 关键字后指明要被连接的表，而在 ON 关键字后则指定了进行连接时所使用的条件。由于T_Customer 和 T_Order 表中都有名称为 FId 的列，所以在 ON 关键字后的条件中使用 FId 字段的时候必须显示的指明这里使用 FId 字段属于哪个表。比如下面的 sql 语句在执行的时候则会报出“列名 FId 不明确”的错误信息：
FCustomerId= FId
同样如果在 SELECT 语句后的字段列表中也不能存在有歧义的字段，比如下面的 sql 语句执行会出错：
FId,FNumber,0); font-style:normal; background-color:inherit">必须为 FId 字段显式的指定所属的表，修正后的 sql 语句如下：
T_Order.FId,0); font-style:normal; background-color:inherit">
为了避免列名歧义并且提高可读性，这里建议使用表连接的时候要显式列所属的表，如下：
 T_Order.FCustomerId= T_Customer.FId
指定列所属的表后，我们就可以很轻松的引用同名的字段了，比如下面的 sql 语句检索所有的订单以及它们对应的客户的相关信息：
 T_Customer.FId,T_Customer.FName,T_Customer.FAge
T_Order.FCustomerId= T_Customer.FId
在大多数数据库系统中，INNER JOIN 中的 INNER 是可选的，INNER JOIN 是默认的连接方式。也就是下面的 sql 语句同样可以完成和检索所有的订单以及它们对应的客户的相关信息的功能：
JOIN为了明确指定字段所属的表，上面的 sql 语句中多次出现了 T_Order、T_Customer，当字段比较多的时候这样的 sql 语句看起来非常繁杂，为此可以使用表别名来简化 sql 语句的编写，比如下面的 sql 语句就与上面的 sql 语句是等价的：
o.FId,o.FNumber,o.FPrice,0); font-style:normal; background-color:inherit"> c.FId,c.FName,c .FAge
 FROMT_Order oJOINT_Customer c
ONo.FCustomerId= c.FId

在使用表连接的时候可以不局限于只连接两张表，因为有很多情况下需要联系许多表。
不等值连接
等值连接，也就是在这种连接的ON子句的条件包含一个等号运算。等值连接是最常用的连接，因为它指定的连接条件是一个表中的一个字段必须等于另一个表中的一个字段。
处理等值连接， 还存在另外一种不等值连接， 也就是在连接的条件中可以使用小于 （<） 、大于（>） 、不等于（<>）等运算符，而且还可以使用LIKE、BETWEEN AND等运算符，甚至还可以使用函数。
例如，如果需要检索价格小于每个客户的年龄的五倍值的订单列表，那么就可以使用不等值连接，实现的sql语句如下所示：
SELECTT_Order.FNumber,
T_Customer.FName,T_Customer.FAge
FROMT_Order
INNER JOINT_Customer
T_Order.FPrice< T_Customer.FAge*5
执行完毕我们就能在输出结果中看到下面的执行结果：
FNumber FPrice FName FAge
K001 100.00 TOM 21
K001 100.00 MIKE 24
K001 100.00 JACK 30
K001 100.00 TOM 25
N002 100.00 TOM 21
N002 100.00 MIKE 24
N002 100.00 JACK 30
N002 100.00 TOM 25
T002 100.00 TOM 21
T002 100.00 MIKE 24
T002 100.00 JACK 30
T002 100.00 TOM 25
不等值连接产生了大量的查询结果，因为它是对被连接的两张表做了笛卡尔运算，所以如果只想查看与客户对应的订单，那么就要在不等值连接后添加等值连接匹配条件。实现的sql语句如下： SELECTT_Order.FNumber,T_Customer.FAge FROMT_Order INNER JOINT_Customer ONT_Order.FPrice< T_Customer.FAge*5 andT_Order.FCustomerId=T_Customer.FId这里添加了“andT_Order.FCustomerId=T_Customer.FId”这个条件来限制匹配规则。执行完毕我们就能在输出结果中看到下面的执行结果： FNumber FPrice FName FAge K001 100.00 TOM 21 N002 100.00 MIKE 24交叉连接与内连接比起来，交叉连接非常简单，因为它不存在ON子句。交叉连接会将涉及到的所有表中的所有记录都包含在结果集中。可以采用两种方式来定义交叉连接，分别是隐式的和显式的。隐式的连接只要在SELECT语句的FROM语句后将要进行交叉连接的表名列出即可，这种方式可以被几乎任意数据库系统支持。比如下面的sql语句为将T_Customer表和T_Order做交叉连接：
SELECT T_Customer.FId,T_Customer.FAge,
T_Order.FId,T_Order.FPrice
FROM T_Customer,T_Order
执行完毕我们就能在输出结果中看到下面的执行结果： FId FName FAge FId FNumber FPrice 1 TOM 21 1 K001 100.00 1 TOM 21 2 K002 200.00 1 TOM 21 3 T003 300.00 1 TOM 21 4 N002 100.00 1 TOM 21 5 N003 500.00在交叉连接中同样可以对表使用别名，比如上面的sql语句来代替：
SELECT c.FId,c.FAge,0); font-style:normal; background-color:inherit"> o.FId,o.FPrice FROM T_Customer c,T_Order o
执行完毕我们就能在输出结果中看到下面的执行结果，可以看到执行结果与上面的一模一样：交叉连接的显式定义方式为使用CROSS JOIN关键字，其语法与INNER JOIN类似，比如下面的sql将T_Customer表和T_Order做交叉连接： SELECTT_Customer.FId,T_Order.FId,T_Order.FPrice FROMT_Customer CROSS JOINT_Order使用CROSS JOIN的方式声明的交叉连接只能被MysqL、MSsqlServer和Oracle所支持，在DB2中是不被支持的。因为所有的数据库系统都支持隐式的交叉连接，所以它是执行交叉连接的最好方法。

自连接到目前为止，我们讲解的连接都是在不同的数据表之间进行的，其实参与连接的表完全可以是同一样表，也就是表与其自身相连接，这样连接就被称为自连接。自连接并不是独立于交叉连接、内连接、外连接等这些连接方式之外的另外一种连接方式，而只是这些连接方式的一种特例，也就是交叉连接、内连接、外连接等连接方式中只要参与连接的表是同一张表，那么它们就可以被称为自连接。虽然大部分时间使用连接都是在连接不同的表，但是有的时候表也需要与自身连接，其主要用途就是检索一张表内部的匹配情况。下面就通过一个实例来演示自连接的使用。假设需要检索与另外一个订单的订单类型一样的所有订单的列表。 有的开发人员可能会写出下面的sql语句： SELECTFNumber,FPrice,FTypeId FROMT_Order WHERE FTypeId= FTypeId执行以后我们在输出结果中看到下面的执行结果：FNumber FPrice FTypeIdK001 100.00 1K002 200.00 1T003 300.00 2N002 100.00 2N003 500.00 4T001 300.00 3T002 100.00 1这里显示出了T_Order表中的所有数据，而不是想像中的结果。因为这里的WHERE语句条件永远为真，因为向同行的相同列总是等于它自己，因此结果集中包含了表中的所有记录。如果要实现要求的功能，可以假想存在另外一个与T_Order表完全相同的表，这样我们就可以在这两个表之间进行任意的连接操作了。我们尝试套用INNER JOIN的写法，只是将参与连接的两个表名都设置为T_Order，sql语句如下： SELECTFNumber,FTypeId FROMT_Order INNER JOINT_Order ONT_Order.FTypeId=T_Order.FTypeId这句sql语句执行以后数据库系统会报出如下的错误信息：FROM 子句中的对象 "T_Order" 和 "T_Order" 具有相同的表现名称。请使用相关名称来区分它们。很显然，因为这里两次使用了T_Order表，但是数据库系统无法区分这两个T_Order表，因此必须为它们指定不同的别名，修改后的sql语句如下： SELECTo1.FNumber,o1.FPrice,o1.FTypeId,o2.FNumber,o2.FPrice,o2.FTypeId FROMT_Order o1 INNER JOINT_Order o2 ONo1.FTypeId=o2.FTypeId这里为T_Order表取了两个别名o1和o2，并且在引用表中列的时候也明确的指定了列属于那个表下的，这样数据库系统就能区分这两个别名代表的表了。使用别名以后我们可以将这两个别名看作结构相同、数据相同的两个不同的表，这样就可以避免思维上的障碍。上边的sql语句执行以后我们在输出结果中看到下面的执行结果：
FNumber FPrice FTypeId FNumber FPrice FTypeId
K001 100.00 1 K001 100.00 1
K002 200.00 1 K001 100.00 1
T002 100.00 1 K001 100.00 1
K001 100.00 1 K002 200.00 1
K002 200.00 1 K002 200.00 1
T002 100.00 1 K002 200.00 1
T003 300.00 2 T003 300.00 2
N002 100.00 2 T003 300.00 2
T003 300.00 2 N002 100.00 2
N002 100.00 2 N002 100.00 2
N003 500.00 4 N003 500.00 4
T001 300.00 3 T001 300.00 3
K001 100.00 1 T002 100.00 1
K002 200.00 1 T002 100.00 1
T002 100.00 1 T002 100.00 1
这个sql语句执行成功，没有语法错误，它是一个有效的自连接，不过它执行所产生的结果却不是正确的。比如第一行中“订单号为K001的订单与订单号为K001的订单的订单类型相同” ，自己的订单类型当然与自己相同，这当然是正确的，可是这样的结果对我们来说是没有意义的。ON子句中指定两个表的FTypeId字段必须相同，当然对于同一个订单来说，它们肯定是相同的，而这里真正要查询的是具有相同的FTypeId字段值的两个不同的订单，因此需要在连接条件中添加一个新的条件，修改后的sql语句如下： SELECTo1.FNumber,o2.FTypeId FROMT_Order o1 INNER JOINT_Order o2 ONo1.FTypeId=o2.FTypeIdando1.FId<>o2.FIdON子句末端添加的新条件“ando1.FId<>o2.FId”检查了别名为o1的表的主键不等于名为o2的表的主键，因为主键是唯一的，所以这样就可以确保得到的是一个不同的订单，从而不包含同一张订单。这个sql语句执行以后我们在输出结果中看到下面的执行结果：
FNUMBER FPRICE FTYPEID FNUMBER FPRICE FTYPEID
T002 100 1 K001 100 1
K002 200 1 K001 100 1
T002 100 1 K002 200 1
K001 100 1 K002 200 1
N002 100 2 T003 300 2
T003 300 2 N002 100 2
K002 200 1 T002 100 1
K001 100 1 T002 100 1
可以看到执行结果中已经去掉了相同订单的匹配， 但是仔细观察仍然会发现存在重复的行。比如第一行的最后一行。o1表中的T002订单与o2表中的K001订单匹配，然后o2表中的K001订单与o1表中的T002订单匹配，也就是说数据库系统把“A匹配B”与“B匹配A”看成了两个不同的匹配，而实质上它们只是方向不同的相同的匹配，因此需要防止出现这样相同的匹配结果。因为出现上面这种问题的原因是因为存在“A匹配B”与“B匹配A”这两个方向的匹配，那么我们只要破坏这种双向匹配就可以了，最简单的方式就是要求o1的表的主键值于o2的表的主键值。修改后的sql语句如下： SELECTo1.FNumber,o2.FTypeId FROMT_Order o1 INNER JOINT_Order o2 ONo1.FTypeId=o2.FTypeIdando1.FId<o2.FId这里仅有的改变是ON子句中连接条件的最后面部分，其原来的形式是： o1.FId<>o2.FId这个ON子句仅仅应用于两个表中FId字段值不同的记录。只要o1表与o2表中的FId字段值不同，则记录就会被包含字结果集中，因此将导致重复，所以这里将ON子句的这个sql片段替换为： o1.FId<o2.FId现在o1表的一个记录行仅仅在它的FId字段值小于o2表的一个记录行仅仅在它的FId字段值的时候，才出现在结果集中。这确保了一行数据仅出现在结果集中一次。这个sql语句执行以后我们在输出结果中看到下面的执行结果： FNUMBER FPRICE FTYPEID FNUMBER FPRICE FTYPEID K001 100 1 K002 200 1 T003 300 2 N002 100 2 K002 200 1 T002 100 1 K001 100 1 T002 100 1
外部连接内部连接要求组成连接的两个表必须具有匹配的记录，T_Order表中的数据如下：
FId FNumber FPrice FCustomerId FTypeId
1 K001 100.00 1 1
2 K002 200.00 1 1
3 T003 300.00 1 2
4 N002 100.00 2 2
5 N003 500.00 3 4
6 T001 300.00 4 3
7 T002 100.00 <NULL> 1
使用内部连接可以查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，sql语句如下：
SELECTo.FNumber,o.FCustomerId,c.FAge FROMT_Order o INNER JOINT_Customer c ONo.FCustomerId=c.FId执行以后我们在输出结果中看到下面的执行结果： FNumber FPrice FCustomerId FName FAge K001 100.00 1 TOM 21 K002 200.00 1 TOM 21 T003 300.00 1 TOM 21 N002 100.00 2 MIKE 24 N003 500.00 3 JACK 30 T001 300.00 4 TOM 25仔细观察我们可以看到T_Order表中有7行数据，而通过上面的内部连接查询出来的结果只有6条，其中订单号为T002的订单没有显示到结果集中。这是因为订单号为T002的订单的FCustomerId字段值为空，显然是无法与T_Customer表中的任何行匹配了，所以它没有显示到结果集中。在一些情况下这种处理方式能够满足要求，但是有时我们要求无法匹配的NULL值也要显示到结果集中，比如“查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，如果没有对应的客户，则在客户信息处显示空格” ，希望的查询结果是这样的： FNumber FPrice FCustomerId FName FAge K001 100.00 1 TOM 21 K002 200.00 1 TOM 21 T003 300.00 1 TOM 21 N002 100.00 2 MIKE 24 N003 500.00 3 JACK 30 T001 300.00 4 TOM 25 T002 100.00 <NULL> <NULL> <NULL>使用内部连接是很难达到这种效果的，可以使用外部连接来实现。外部连接主要就是用来解决这种空值匹配问题的。外部连接的语法与内部连接几乎是一样的，主要区别就是对于空值的处理。外部连接不需要两个表具有匹配记录，这样可以指定某个表中的记录总是放到结果集中。根据哪个表中的记录总是放到结果集中，外部连接分为三种类型：右外部连接（RIGHT OUTER JOIN） 、左外部连接（LEFT OUTER JOIN）和全外部连接（FULL OUTER JOIN） 。三者的共同点是都返回符合连接条件的数据，这一点是和内部连接是一样的，不同点在于它们对不符合连接条件的数据处理，三者不同点说明如下： 左外部连接还返回左表中不符合连接条件的数据；
  • 左外部连接还返回左表中不符合连接条件的数据；
  • 左外部连接还返回右表中不符合连接条件的数据；
  • 全外部连接还返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据， 它其实是左外部连接和左外部连接的合集。
这里的左表和右表是相对于JOIN关键字来说的，位于JOIN关键字左侧的表即被称为左 表，而位于JOIN关键字右侧的表即被称为右表。比如：
SELECT o.FNumber,
c.FName,c.FAge
FROM T_Order o
INNER JOIN T_Customer c
ON o.FCustomerId=c.FId
这里的T_Order就是左表，而T_Customer则是右表。

左外部连接
在左外部连接中，左表中所有的记录都会被放到结果集中，无论是否在右表中存在匹配记录。比如下面的sql语句用来实现“查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，如果没有对应的客户，则在客户信息处显示空格” ：
SELECTo.FNumber,
c.FName,c.FAge
FROMT_Order o
LEFT OUTER JOINT_Customer c
ONo.FCustomerId=c.FId

在T_Order表中有7条记录，其中最后一条不满足连接条件，但是也放到了结果集中，只是在不存在匹配条件的列中显示为NULL。
虽然左外部连接包含左表中的所有记录，但是它只提供出示的结果集，WHERE语句仍然会改变最终的结果集。比如为上面的sql语句添加一个WHERE子句，使得结果中不包含价格小于150元的订单：
SELECTo.FNumber,c.FAge
FROMT_Order o
LEFT OUTER JOINT_Customer c
ONo.FCustomerId=c.FId
WHERE o.FPrice>=150
执行以后我们在输出结果中看到下面的执行结果：
FNumber FPrice FCustomerId FName FAge
K002 200.00 1 TOM 21
T003 300.00 1 TOM 21
N003 500.00 3 JACK 30
T001 300.00 4 TOM 25
尽管左外部连接返回了T_Order表中的所有记录，但是由于WHERE语句的过滤，包括订单号为T002在内的所有价格小于150元的订单全部被排除在了结果集之外。

右外部连接
与左外部连接正好相反， 在右外部连接中不管是否成功匹配连接条件都会返回右表中的所有记录。比如下面的sql语句使用右外部连接查询每张订单的信息以及对应的客户信息：
SELECTo.FNumber,c.FAge
FROMT_Order o
RIGHT OUTER JOINT_Customer c
ONo.FCustomerId=c.FId
全外部连接
几乎所有的数据库系统都支持左外部连接和右外部连接， 但是全外部连接则不是所有数据库系统都支持，特别是最常使用的MysqL就不支持全外部连接。全外部连接是左外部连接和右外部连接的合集，因为即使在右表中不存在匹配连接条件的数据，左表中的所有记录
也将被放到结果集中，同样即使在左表中不存在匹配连接条件的数据，右表中的所有记录也
将被放到结果集中。
比如下面的sql语句使用全外部连接查询每张订单的信息以及对应的客户信息：
SELECTo.FNumber,c.FAge
FROMT_Order o
FULL OUTER JOINT_Customer c
ONo.FCustomerId=c.FId
执行以后我们在输出结果中看到下面的执行结果：
FNUMBER FPRICE FCUSTOMERID FNAME FAGE
K001 100.00 1 TOM 21
K002 200.00 1 TOM 21
T003 300.00 1 TOM 21
N002 100.00 2 MIKE 24
N003 500.00 3 JACK 30
T001 300.00 4 TOM 25
<NULL> <NULL> <NULL> LINDA <NULL>
T002 100.00 <NULL> <NULL> <NULL>
可以看到前6条记录都是符合连接条件的，而T_Customer表中姓名为LINDA的客户没有对应的订单，但是仍然被放到了结果集中，其无法匹配的字段填充的都是NULL，同样订单号为T002的订单没有匹配任何一个客户，但是仍然被放到了结果集中。
虽然在MysqL中不支持全外部连接，不过由于全外部连接是左外部连接和右外部连接的合集，所以可以使用左外部连接和右外部连接来模拟实现全外部连接：使用左外部连接和右外部连接分别进行匹配查询，然后使用UNION运算符来取两个查询结果集的合集。比如可以在MysqL中执行下面的sql来实现T_Order表和T_Customer表的全外部连接：
SELECTo.FNumber,c.FAge
FROMT_Order o
LEFT OUTER JOINT_Customer c
ONo.FCustomerId=c.FId
UNION
SELECTo.FNumber,c.FAge
FROMT_Order o
RIGHT OUTER JOINT_Customer c
ONo.FCustomerId=c.FId
执行以后我们在输出结果中看到下面的执行结果：
FNUMBER FPRICE FCUSTOMERID FNAME FAGE
K001 100.00 1 TOM 21
K002 200.00 1 TOM 21
T003 300.00 1 TOM 21
N002 100.00 2 MIKE 24
T001 300.00 4 TOM 25
N003 500.00 3 JACK 30
<NULL> <NULL> <NULL> LINDA <NULL>
T002 100.00 <NULL> <NULL> <NULL>
可以看到和全外部连接的执行结果是完全一致的。

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问