Join是关系型数据库系统的重要操作之一，SQL常用Join：内联接、外联接和交叉联接等。
这里讨论一下这常用的三种连接。
测试环境：db2 v10.1, linux
表定义：
--用户
CREATE TABLE USER
(
USERID INTEGER NOT NULL,
COMPANYID INTEGER,
TELNO VARCHAR(12)
); --公司
CREATE TABLE COMPANY
(
COMPANYID INTEGER NOT NULL,
TELNO VARCHAR(12)
);
数据：
--USER USERID COMPANYID TELNO
----------- ----------- ------------
11 2 777777
22 3 123456
33 4 567890 --COMPANY COMPANYID TELNO
----------- ------------
2 888888
1. inner join
[db2inst1@win ~]$ db2 "select * from user inner join company on user.companyid=company.companyid" USERID COMPANYID TELNO COMPANYID TELNO
----------- ----------- ------------ ----------- ------------
11 2 777777 2 888888 1 record(s) selected.
注意：内联接（Inner join）满足交换律：“A inner join B” 和 “B inner join A” 是相等的。
查看访问计划：
Optimized Statement:
-------------------
SELECT
Q2.USERID AS "USERID",
Q2.COMPANYID AS "COMPANYID",
Q2.TELNO AS "TELNO",
Q1.COMPANYID AS "COMPANYID",
Q1.TELNO AS "TELNO"
FROM
DB2INST1.COMPANY AS Q1,
DB2INST1.USER AS Q2
WHERE
(Q2.COMPANYID = Q1.COMPANYID) Access Plan:
-----------
Total Cost: 13.5566
Query Degree: 1 Rows
RETURN
( 1)
Cost
I/O
|
1
HSJOIN
( 2)
13.5566
2
/-----+------\
3 1
TBSCAN TBSCAN
( 3) ( 4)
6.77858 6.77776
1 1
| |
3 1
TABLE: DB2INST1 TABLE: DB2INST1
USER COMPANY
Q2 Q1
用HSJOIN的方式进行，DB2对此进行了重写。
2. outer join
外部联接保存一个或两个输入表的所有行，即使无法找到匹配联接谓词的行。
[db2inst1@win ~]$ db2 "select * from user left outer join company on user.companyid=company.companyid" USERID COMPANYID TELNO COMPANYID TELNO
----------- ----------- ------------ ----------- ------------
11 2 777777 2 888888
33 4 567890 - -
22 3 123456 - - 3 record(s) selected.
[db2inst1@win ~]$ db2 "select * from user right outer join company on user.companyid=company.companyid" USERID COMPANYID TELNO COMPANYID TELNO
----------- ----------- ------------ ----------- ------------
11 2 777777 2 888888 1 record(s) selected.
查看访问计划：
Optimized Statement:
-------------------
SELECT
Q2.USERID AS "USERID",
Q2.COMPANYID AS "COMPANYID",
Q2.TELNO AS "TELNO",
Q1.COMPANYID AS "COMPANYID",
Q1.TELNO AS "TELNO"
FROM
DB2INST1.COMPANY AS Q1
LEFT OUTER JOIN DB2INST1.USER AS Q2
ON (Q2.COMPANYID = Q1.COMPANYID) Access Plan:
-----------
Total Cost: 13.5566
Query Degree: 1 Rows
RETURN
( 1)
Cost
I/O
|
1
HSJOIN<
( 2)
13.5566
2
/-----+------\
3 1
TBSCAN TBSCAN
( 3) ( 4)
6.77858 6.77776
1 1
| |
3 1
TABLE: DB2INST1 TABLE: DB2INST1
USER COMPANY
Q2 Q1
这里也是用的HSJOIN。
3. cross join
交叉联接（cross join）执行两个表的笛卡尔积（就是把表A和表B的数据进行一个N*M的组合）。也就是说，它匹配一个表与另一个表中的每一行；我们不能通过使用ON子句在交叉联接指定谓词，虽然我们可以使用WHERE子句来实现相同的结果，这是交叉联接基本上是作为一个内部联接了。
[db2inst1@win ~]$ db2 "select * from user cross join company" USERID COMPANYID TELNO COMPANYID TELNO
----------- ----------- ------------ ----------- ------------
11 2 777777 2 888888
22 3 123456 2 888888
33 4 567890 2 888888 3 record(s) selected.
查看访问计划：
Optimized Statement:
-------------------
SELECT
Q2.USERID AS "USERID",
Q2.COMPANYID AS "COMPANYID",
Q2.TELNO AS "TELNO",
Q1.COMPANYID AS "COMPANYID",
Q1.TELNO AS "TELNO"
FROM
DB2INST1.COMPANY AS Q1,
DB2INST1.USER AS Q2 Access Plan:
-----------
Total Cost: 13.5563
Query Degree: 1 Rows
RETURN
( 1)
Cost
I/O
|
3
NLJOIN
( 2)
13.5563
2
/-----+------\
1 3
TBSCAN TBSCAN
( 3) ( 4)
6.77776 6.77858
1 1
| |
1 3
TABLE: DB2INST1 TABLE: DB2INST1
COMPANY USER
Q1 Q2
看到这里使用的是NLJOIN。