Oracle中的连接查询讲解

最近在写需求的时候遇上一个连接查询的问题，趁此机会讲解下Oracle中的连接查询

Oracle中主要的连接操作

连接类型	说明
INNER JOIN	内联接，结果为两个联接表中的匹配行的联接
LEFT JOIN	左联接：结果包括左表（出现在JOIN子句最左边）中的所有行，不包括右表中的不匹配行
RIGHT JOIN	右联接：结果包括右表（出现在JOIN子句最右边）中的所有行，不包括左表中的不匹配行
FULL JOIN	完全联接：结果包括所有联接中的所有行，不论他们是否匹配
CROSS JOIN	交叉联接：结果包括两个联接表中的所有可能的行组合，交叉连接返回的是两个表的笛卡儿积（Oracle不支持）
NATURAL JOIN	自然连接时在两张表中寻找那些数据类型和列名都相等的字段，然后自动地将他们连接起来

不同的联接类型决定了如何处理联接条件中不匹配的元素，接下来就以左联接为例来描述我所遇到的问题。

示例所需SQL脚本

此处直接选用了我在项目中的数据脚本，有需要的可以直接download下来操作

在Idea 中执行SQL脚本

首先将所下载的sql脚本放入工程目录中，如下图：

SQL脚本所在工程目录结构.png
在打开的SQL文件中按 Ctrl + Shift + F10 在弹出的页面中选择在哪个库执行即可（本列使用的是oracle数据库）

数据库选择.png

本人所遇问题描述

问题1：

执行SQL片段1

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME AS createTm
FROM FORE_TICKET_CANCEL FTC
  LEFT  JOIN  FORE_TICKETCANCEL_SEGMENT FTS
    ON FTC.FTC_TICKETCANCEL_NO =  FTS.FTCS_TICKETCANCEL_NO
    AND FTC.FTC_TA_NO='2019021900003';--查询结果 3657条记录

SELECT FTC.* FROM FORE_TICKET_CANCEL FTC;-- 主表（左表FTC）记录总数 3657

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKETCANCEL_SEGMENT FTS
  LEFT JOIN FORE_TICKET_CANCEL FTC
    ON FTS.FTCS_TICKETCANCEL_NO = FTC.FTC_TICKETCANCEL_NO
    AND FTC.FTC_TA_NO='2019021900003';--查询结果 4669条记录

SELECT FTS.* FROM FORE_TICKETCANCEL_SEGMENT FTS;-- 主表（左表FTS）记录总数 4669

执行SQL片段2

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKET_CANCEL FTC
  LEFT  JOIN  FORE_TICKETCANCEL_SEGMENT FTS
  ON FTC.FTC_TICKETCANCEL_NO =  FTS.FTCS_TICKETCANCEL_NO;--查询结果 4669条记录

SELECT FTC.* FROM FORE_TICKET_CANCEL FTC;-- 主表（左表FTC）记录总数 3657

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKETCANCEL_SEGMENT FTS
  LEFT JOIN FORE_TICKET_CANCEL FTC
  ON FTS.FTCS_TICKETCANCEL_NO = FTC.FTC_TICKETCANCEL_NO;--查询结果 4669条记录

SELECT FTS.* FROM FORE_TICKETCANCEL_SEGMENT FTS;-- 主表（左表FTS）记录总数 4669

执行SQL片段1的时候，所产生的结果大家可能没什么异议，左连接查询以主表数据为主，所以连接查询的结果与主表记录条数一致容易理解，然后当我们去掉SQL片段1的AND条件后，发现SQL片段2中的第一段SQL执行结果与主表中的记录条数不一致。

根据如上对左连接的定义左联接查询出的结果包括左表（出现在JOIN子句最左边）中的所有行，不包括右表中的不匹配行。SQL片段2中的第一段SQL查询出的结果与右表中的总记录条数一致，说明了右表中的数据全部匹配上了。为了证实这个结论，我们只需将右边中的数据修改一下，使其中一条数据与左表关联不上，那么最后的查询结果应该是比4669少一条的。

首先，我们通过如下图所示的SQL查询出在右表中出现多次的FTCS_TICKETCANCEL_NO（因为联结是基于这个字段）。

SQL与查询结果.png

第二步，选取其中一条结果为条件去查询右表（FTS）中记录，并修改其中一条记录的FTCS_TICKETCANCEL_NO字段值，使其与左表关联不上。

修改前.png

修改后.png

修改后的值我们要记住，因为要之后要还原进行后续的操作。然后再执行SQL片段2中的第一条SQL如果查询出的结果为4668条的话说明我们的猜测成立。

查询结果.png

问题2：

接着我尝试修改了下如下SQL片段3中AND后的FTC_TA_NO参数值，又发现不一样的结果。在进行以下操作之前记得恢复问题1中的数据修改。

SQL片段3

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKET_CANCEL FTC
  LEFT  JOIN  FORE_TICKETCANCEL_SEGMENT FTS
    ON FTC.FTC_TICKETCANCEL_NO =  FTS.FTCS_TICKETCANCEL_NO
    AND FTC.FTC_TA_NO='2019021900003';--查询结果 3657条记录

SELECT FTC.* FROM FORE_TICKET_CANCEL FTC;-- 主表（左表FTC）记录总数 3657

SQL片段4

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKET_CANCEL FTC
  LEFT  JOIN  FORE_TICKETCANCEL_SEGMENT FTS
    ON FTC.FTC_TICKETCANCEL_NO =  FTS.FTCS_TICKETCANCEL_NO
    AND FTC.FTC_TA_NO='2019021900004';--查询结果 3660条记录

SELECT FTC.* FROM FORE_TICKET_CANCEL FTC;-- 主表（左表FTC）记录总数 3657

SQL片段5

SELECT
  FTC.FTC_TICKETCANCEL_NO,
  FTC.FTC_TA_NO AS TA_NO,
  FTS.FTCS_ARR_DATE AS ARR_DATE,
  FTS.FTCS_DEP_DATE AS DEP_DATE,
  FTC.FTC_CREATEDATATIME as createTm
FROM FORE_TICKET_CANCEL FTC
  LEFT  JOIN  FORE_TICKETCANCEL_SEGMENT FTS
    ON FTC.FTC_TICKETCANCEL_NO =  FTS.FTCS_TICKETCANCEL_NO
    AND FTC.FTC_TA_NO='2019021900005';--查询结果 3658条记录

SELECT FTC.* FROM FORE_TICKET_CANCEL FTC;-- 主表（左表FTC）记录总数 3657

解释之前我们再重申以下左联接查询所产生的结果集：包括左表（出现在JOIN子句最左边）中的所有行，加上右表中的匹配行。

到这里我们应该明白了FORE_TICKET_CANCEL表中的数据与FORE_TICKETCANCEL_SEGMENT中的数据是 1 ： N的关系。

首先，我们来看SQL片段3中。SQL再执行查询的时候会根据FTC.FTC_TA_NO='2019021900003' 时所对应的FTC.FTC_TICKETCANCEL_NO 在右表（FTS）中的记录条数来生成查询结果。