达梦反连接与半连接

半连接与反连接

除了内连接，外连接等还有两种特殊的连接查询：半连接（Semi Join）和反连接（Anti Join）。由于 SQL 标准没有定义这两种连接查询语法，而是通过子查询的方式实现相同的效果。

1.半连接

半连接返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。半连接的示意图如下：

table1 中的 id = 2 在 table2 中没有对应的数据，所以连接的结果不包含该记录。

半连接只会返回左表中的数据，右表只用于条件判断。另外，即使右表中存在多个匹配的数据，左边中的数据只返回一次。半连接通常用于存在性判断，例如哪些顾客购买了产品，而不需要知道他们购买的具体产品和数量。

测试数据

DROP TABLE T1 CASCADE; DROP TABLE T2 CASCADE; CREATE TABLE T1 (ID INTEGER,NAME VARCHAR(128)); CREATE TABLE T2 (ID INTEGER,NAME VARCHAR(128)); begin for i in 1..100000 loop insert into T1 values(i,'dameng'||i); insert into T2 values(i+500,'damengsh'||i); end loop; end;

T1，T2表中各10万条数据。

查询T1表ID在T2表中存在的行数。因为T1的ID是1到100000，T2的ID是501到100500。很显然，sql执行结果是100000-500=99500。执行计划如下：

SQL> EXPLAIN SELECT COUNT(*) FROM T1 A  WHERE A.ID IN (SELECT B.ID FROM T2 B);

通过执行计划我们可以看到先计算T2表的非关联子查询（全表扫描，去重），然后与T1表做HASH2 INNER JOIN。

很显然这个sql我们可以改成exists的关联子查询，如下：

SELECT  count(*) FROM T1 A WHERE  EXISTS(SELECT 1 FROM T2 B where A.ID=B.ID);

查看这个sql的执行计划

可以看到，与上一个SQL执行计划一模一样。所以优化器是进行了查询转换，将关联子查询转换成非关联子查询。

我们可以通过HINT让其不进行转换

SELECT /*+ NO_SEMI_GEN_CROSS OPTIMIZER_MODE(1) */ COUNT(*) FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T2 B);

执行计划如下：

1   #NSET2: [41, 1, 4] 2     #PRJT2: [41, 1, 4]; exp_num(1), is_atom(FALSE) 3       #AAGR2: [41, 1, 4]; grp_num(0), sfun_num(1) slave_empty(0) 4         #HASH LEFT SEMI JOIN2: [41, 100000, 4]; KEY_NUM(1);  KEY(A.ID=B.ID) KEY_NULL_EQU(0) 5           #CSCN2: [10, 100000, 4]; INDEX33555553(T1 as A) 6           #CSCN2: [10, 100000, 4]; INDEX33555554(T2 as B)

可以看到执行计划已经变了，先将T1，T2做全表扫描，然后进行HASH LEFT SEMI JOIN。连接条件是A.ID=B.ID。

SEMI_GEN_CROSS：优先采用半连接转换为等价的内连接，仅 OPTIMIZER_MODE=1 有效。

NO_SEMI_GEN_CROSS：不采用半连接转换为等价的内连接，仅 OPTIMIZER_MODE=1 有效。

那这两种那种效率更高呢。从执行时间来看第二种更快。

SELECT  COUNT(*) FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T2 B);   SELECT /*+ NO_SEMI_GEN_CROSS * OPTIMIZER_MODE(1)*/ COUNT(*) FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T2 B);

如果我们将表数据库做成1000万。可以看到还是半连接的效率要好点。

SELECT  COUNT(*) FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T2 B);   SELECT /*+ NO_SEMI_GEN_CROSS * OPTIMIZER_MODE(1)*/ COUNT(*) FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T2 B);

1. 反连接

反连接返回左表中与右表不匹配的数据行，通常体现为 NOT EXISTS 或者 NOT IN 子查询。反连接的逻辑与半连接正好相反，示意图如下：

table1 中只有 id = 2 在 table2 中没有对应的数据，所以连接的结果返回了该记录。

反连接只会返回左表中的数据，右表只用于条件判断。反查询常见的应用包括：查找没有员工的部门信息，或者没有购买任何产品的顾客信息等。

SQL> EXPLAIN SELECT COUNT(*) FROM T1 A  WHERE A.ID NOT IN (SELECT B.ID FROM T2 B); 1   #NSET2: [4037, 1, 4] 2     #PRJT2: [4037, 1, 4]; exp_num(1), is_atom(FALSE) 3       #AAGR2: [4037, 1, 4]; grp_num(0), sfun_num(1) slave_empty(0) 4         #HASH LEFT SEMI JOIN2: [4037, 1, 4];  (ANTI),KEY_NUM(1);  KEY(A.ID=B.ID) KEY_NULL_EQU(0) 5           #CSCN2: [1057, 10000000, 4]; INDEX33555559(T1 as A) 6           #CSCN2: [1057, 10000000, 4]; INDEX33555560(T2 as B) SQL> EXPLAIN SELECT  count(*) FROM T1 A WHERE  NOT EXISTS(SELECT 1 FROM T2 B where A.ID=B.ID);2    1   #NSET2: [4073, 1, 12] 2     #PRJT2: [4073, 1, 12]; exp_num(1), is_atom(FALSE) 3       #AAGR2: [4073, 1, 12]; grp_num(0), sfun_num(1) slave_empty(0) 4         #HASH LEFT SEMI JOIN2: [4073, 1, 12];  (ANTI),KEY_NUM(1);  KEY(A.ID=B.ID) KEY_NULL_EQU(0) 5           #CSCN2: [1073, 10000000, 12]; INDEX33555559(T1 as A) 6           #CSCN2: [1057, 10000000, 4]; INDEX33555560(T2 as B)

从执行计划来看，两种sql的执行计划完全一样。