表连接，是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个，那么连接的顺序如何呢？数据库会首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。本文将对比不同数据库对表连接的处理方式差异。
【系列文章】
1).表连接分类
上面是 SQL99 语法标准，从语法定义来看，我们可以把表连接分为几种情况。其中，笛卡尔连接比较少见，自然连接为一种语法糖写法，因此下面重点会谈谈其他四种连接。
2).表连接处理方式
在表连接的实现方式上，可以有很多种，包括但不限于：嵌套循环(NESTED LOOP JOIN)、群集连接(CLUSTER JOIN)、排序合并连接(SORT MERGE JOIN)、笛卡尔连接(CARTESIAN JOIN)、哈希连接(HASH JOIN) 、索引连接(INDEX JOIN) 等等。这里比较常见的是：哈希连接(简称 HJ)、嵌套循环（简称 NJ）、排序合并（简称SMJ）。下面以Oracle为例说明下常见的这三种方式。
1).Oracle 表连接说明
❖ 嵌套循环
优化器决定优先被执行的外侧表。这个外侧表也被成为驱动表，以驱动表查询范围中的每一行数据为基准从内侧表中寻找满足查询条件的数据行。在从驱动表中读取满足某些条件的数据行之后，SQL语句中所使用到的关于该驱动表的所有列都获得常量，通过对这些常量和原来就赋予了常量的查询条件的斟酌，为下一步执行选择一个内侧表。如果仍然还存在需要按照该方法执行连接的表，则按照与上面相同的方法来决定连接顺序。如果连接列上有索引，则会对优化器决定连接顺序有重要影响。实际在连接时，需要读取与外侧集合汇中的每一行数据相对应的内侧集合中的所有行。如果可以通过部分范围处理的方式来处理，则在执行过程中可以临时中止连接。对于外侧循环中的每一个数据行，内侧循环中所有满足条件的数据行都需要被执行。
❖ 排序合并
在排序合并连接中，oracle分别将第一源表、第二源表按它们各自要连接的列排序，然后将已经排序的两个源表合并。如果找到匹配的数据，就放到结果集。排序合并是在扫描的同时执行连接，而不是像嵌套循环连接那样为了执行表连接而需要按随机方式读取数据。为了实现表连接，必须先对两个表中将要执行连接的行进行排序。虽然这个方法提高了连接的效率，但是由于排序的存在，也增加了连接的代价。引入排序合并连接的主要目的是为了缩减在嵌套循环连接中发生的随机读取量。排序合并适合在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大(所选的数据超过表记录数的5%)时，排序合并连接将比嵌套循环连更加高效。
❖ 哈希连接
哈希连接是在Oracle 7.3开始引入的，以代替排序合并及嵌套循环方式，提高效率。引入哈希连接，主要是为了解决嵌套循环连接中大量随机读取的问题，又要解决排序合并连接中排序代价过大的问题。在不需要排序的情况下，哈希函数就能够把连接对象集中在一起。可以哈希函数并不直接负责连接任务，而是负责把将要连接的对象提前集中在特定的位置。将具有相同哈希值的数据行集中存储在相同的空间上，该空间被称之为"分区"。在这些分区中必须要进行连接的两个分区成为"分区对"。实际上，哈希连接是以分区对为对象来执行连接操作的。在连接时，将较小分区中的数据行读入内存中并为其创建临时哈希表，然后将哈希表中的行视为内侧循环，较大分区中的行视为外侧循环来执行哈希连接。哈希连接适合于小结果集连接到大结果集的情况或者两个都为大结果集的情况，但存在先天限制就是仅适用于等值连接。
2).Oracle 测试示例
-- 内连接（嵌套循环） SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 46 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
-- 内连接（排序合并）
SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 449K| 18 (6)| 00:00:01 |
| 1 | MERGE JOIN | | 10000 | 449K| 18 (6)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 10000 | 302K| 16 (7)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
-- 内连接（哈希连接）
SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 205K| 18 (6)| 00:00:01 |
|* 1 | HASH JOIN | | 10000 | 205K| 18 (6)| 00:00:01 |
| 2 | VIEW | index$_join$_001 | 100 | 1000 | 3 (34)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
| 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 左外连接（哈希外连接）
SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 205K| 18 (6)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER | | 10000 | 205K| 18 (6)| 00:00:01 |
| 2 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
| 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 全外连接（哈希全连接）
SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
SQL> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 673K| 18 (6)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 10000 | 673K| 18 (6)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER | | 10000 | 205K| 18 (6)| 00:00:01 |
| 3 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 |
|* 4 | HASH JOIN | | | | | |
| 5 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
-- 半连接(排序合并半连接)
SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 18 (6)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 100 | 2300 | 18 (6)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
-- 反连接(排序合并反连接)
SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 1 | 23 | 18 (6)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> alter table emp modify dept_id null;
SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);
SQL> select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 |
| 1 | MERGE JOIN ANTI NA | | 1 | 23 | 18 (6)| 00:00:01 |
| 2 | SORT JOIN | | 100 | 1500 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |
|* 5 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
* 设置为可没空后，优化器选择了MERGE JOIN ANTI NA
下文将对国产数据库（含MySQL）做测试对比。在之前先看下结论，国产数据库在表关联能力方面尚可，MySQL在这方面差距相对较大。但国产数据库在优化策略制定上，明显比较单一，不如 Oracle 会根据不同语句制定有针对性的执行计划。此外，在同样架构、同等数据规模，都收集统计信息的情况下，国产库在不同连接方式下差异也很明显，特别是都不太考虑排序合并的方式。下表是针对四种连接方式，共计八条语句下各数据库的表现情况。如统一连接方式下执行计划相同，则只显示一次。
1).MySQL
-- 内连接（嵌套循环） SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
-- 内连接（嵌套循环）
SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |
| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
-- 内连接（嵌套循环）
SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |
| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
-- 左外连接（嵌套循环）
SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |
| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
-- 全外连接（嵌套循环+哈希连接）
mysql> select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join dept d on e.dept_id=d.dept_id' at line 1
* MySQL 中不支持full outer join，只能用下面的方式等价实现
mysql> explain
SELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id
UNION ALL
SELECT e.emp_name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL;
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+
| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL
| 1 | PRIMARY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL
| 2 | UNION | d | NULL | index | NULL | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index
| 2 | UNION | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+
-- 半连接(嵌套循环+子查询物化)
mysql> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+
-- 反连接(嵌套循环+子查询物化)
mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+
| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | Using where; Not exists |
| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10109 | 100.00 | Using where |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+
mysql> alter table emp modify dept_id int null;
mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where |
| 2 | SUBQUERY | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
* 退化为嵌套循环+子查询
2).DM
-- 内连接（哈希内连接） SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
1 #NSET2: [1, 1, 277]
2 #PRJT2: [1, 1, 277]; exp_num(8), is_atom(FALSE)
3 #HASH2 INNER JOIN: [1, 1, 277]; RKEY_UNIQUE KEY_NUM(1); KEY(E.DEPT_ID=D.DEPT_ID) KEY_NULL_EQU(0)
4 #NEST LOOP INDEX JOIN2: [1, 1, 277]
5 #ACTRL: [1, 1, 277]
6 #BLKUP2: [1, 1, 151]; INDEX33555485(E)
7 #SSEK2: [1, 1, 151]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(111),exp_cast(111)], is_global(0)
8 #BLKUP2: [1, 1, 30]; INDEX33555481(D)
9 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[E.DEPT_ID,E.DEPT_ID], is_global(0)
10 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)
-- 内连接（哈希内连接）
SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
1 #NSET2: [3, 10000, 277]
2 #PRJT2: [3, 10000, 277]; exp_num(8), is_atom(FALSE)
3 #HASH2 INNER JOIN: [3, 10000, 277]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)
5 #CSCN2: [1, 10000, 151]; INDEX33555484(EMP as E); btr_scan(1)
-- 内连接（哈希内连接）
SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
1 #NSET2: [2, 10000, 156]
2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)
3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)
5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)
-- 左外连接（哈希外连接）
SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
1 #NSET2: [2, 10000, 78]
2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)
3 #HASH RIGHT JOIN2: [2, 10000, 78]; key_num(1), ret_null(0), KEY(D.DEPT_ID=E.DEPT_ID)
4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)
5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)
-- 全外连接（哈希全连接）
SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
1 #NSET2: [2, 10000, 78]
2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)
3 #HASH FULL JOIN2: [2, 10000, 78]; key_num(1), mix_aagr(0), mix_dist(0) KEY(D.DEPT_ID=E.DEPT_ID)
4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)
5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)
-- 半连接(哈希半连接)
SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);
1 #NSET2: [2, 100, 138]
2 #PRJT2: [2, 100, 138]; exp_num(4), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [2, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)
5 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1
6 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
-- 反连接(哈希半连接)
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
1 #NSET2: [2, 1, 138]
2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193480.colname) KEY_NULL_EQU(0)
4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)
5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)
6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1
7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
SQL> alter table emp modify dept_id null;
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
1 #NSET2: [2, 1, 138]
2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193500.colname) KEY_NULL_EQU(0)
4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)
5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)
6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1
7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
3).KingBase
-- 内连接（哈希连接） SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=8.46..10.73 rows=1 width=71)
Hash Cond: (d.dept_id = e.dept_id)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)
-> Hash (cost=8.44..8.44 rows=1 width=42)
-> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42)
Index Cond: (emp_id = '111'::numeric)
-- 内连接（哈希连接）
SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=3.25..22914.40 rows=990099 width=71)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=42)
-> Hash (cost=2.00..2.00 rows=100 width=29)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)
-- 内连接（哈希连接）
SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=3.25..22914.40 rows=990099 width=28)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=18)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 左外连接（哈希外连接）
SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
QUERY PLAN
----------------------------------------------------------------------
Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=18)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 全外连接（哈希全连接）
SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
QUERY PLAN
----------------------------------------------------------------------
Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=18)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 半连接(嵌套循环半连接)
SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);
QUERY PLAN
------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29)
Join Filter: (dept.dept_id = emp.dept_id)
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)
-> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)
Filter: (salary > '1500'::double precision)
-- 反连接(排序合并反连接)
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
QUERY PLAN
------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29)
Join Filter: (dept.dept_id = emp.dept_id)
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)
-> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)
Filter: (salary > '1500'::double precision)
SQL> alter table emp modify dept_id null;
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=16384.43..40319.75 rows=1 width=29)
One-Time Filter: (NOT $1)
InitPlan 1 (returns $1)
-> Gather (cost=1000.00..16384.43 rows=1 width=0)
Workers Planned: 2
-> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0)
Filter: ((dept_id IS NULL) AND (salary > '1500'::double precision))
-> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29)
Join Filter: (dept.dept_id = emp.dept_id)
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)
-> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)
Filter: (salary > '1500'::double precision)
4).YashanDB
-- 内连接（嵌套循环） SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 内连接（嵌套循环）
SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |
| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 内连接（嵌套循环）
SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |
| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 左外连接（嵌套循环）
SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| |
| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 全外连接（哈希全连接）
SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| |
| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |
| 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 半连接(嵌套循环)
SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| |
| 2 | HASH DISTINCT | | | 50| 37( 0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |
|* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 反连接(排序合并反连接)
SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |
|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |
| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
SQL> alter table emp modify dept_id null;
SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |
|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |
| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
5).Vastbase
-- 内连接（哈希连接） SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=8.28..10.69 rows=1 width=74)
Hash Cond: (d.dept_id = e.dept_id)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)
-> Hash (cost=8.27..8.27 rows=1 width=44)
-> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44)
Index Cond: (emp_id = 111::number)
-- 内连接（哈希连接）
SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
QUERY PLAN
---------------------------------------------------------------------
Hash Join (cost=3.25..351.51 rows=9901 width=74)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44)
-> Hash (cost=2.00..2.00 rows=100 width=30)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)
-- 内连接（哈希连接）
SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;
QUERY PLAN
---------------------------------------------------------------------
Hash Join (cost=3.25..351.51 rows=9901 width=24)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=20)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 左外连接（哈希外连接）
SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;
QUERY PLAN
---------------------------------------------------------------------
Hash Left Join (cost=3.25..351.51 rows=10000 width=24)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=20)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 全外连接（哈希全连接）
SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;
QUERY PLAN
---------------------------------------------------------------------
Hash Full Join (cost=3.25..351.51 rows=10000 width=24)
Hash Cond: (e.dept_id = d.dept_id)
-> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)
-> Hash (cost=2.00..2.00 rows=100 width=20)
-> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 半连接(哈希连接）
SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=255.20..257.91 rows=100 width=30)
Hash Cond: (dept.dept_id = emp.dept_id)
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
-> Hash (cost=254.63..254.63 rows=46 width=8)
-> HashAggregate (cost=254.17..254.63 rows=46 width=8)
Group By Key: emp.dept_id
-> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)
Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
QUERY PLAN
-------------------------------------------------------------------
Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30)
Hash Cond: (emp.dept_id = dept.dept_id)
-> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)
Filter: (salary > 1500::double precision)
-> Hash (cost=2.00..2.00 rows=100 width=30)
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
SQL> alter table emp modify dept_id null;
SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);
QUERY PLAN
------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30)
Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL))
-> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
-> Materialize (cost=0.00..265.33 rows=4467 width=8)
-> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)
Filter: (salary > 1500::double precision)
