SQL 三种基本Join

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。

巴特西

SQL 三种基本Join

最新文章

热门文章