SQL中的各种join

SQL中有各种join操作，例如 inner join, outer join, full join, cross join, natural join等。

---------------------------------------------------------------------------------------------------------------------------------------------

 

inner join内连接 也是最普通的join操作。只有join的两个属性值满足join条件后才能生成新的元组。类似于条件连接。例如select * from tableA inner join tableB on tableA.attributeA = tableB.attributeB 等价于 select * from tableA join tableB on tableA.attributeA = tableB.attributeB也等价于 select * from tableA, tableB where tableA.attributeA = tableB.attributeB

tableA

id attributeA

1     1

2     2

tableB

id attributeB

1    2

3    3

最终结果

tableA.id tableB.id attributeA attributeB

   2             1              2            2

---------------------------------------------------------------------------------------------------------------------------------------------

 

outer join外连接，分为left outer join（简写为left join）和 right outer join*（简写为right join）,左右外连接可以保存一边的所有元组值。即使join的两个属性值不满足join的条件，依然能留下一个表的元组，另外一半属性就填充为NULL.例如tableA.attributeA = tableB.attributeB为连接条件。可是tableA.attributeA含有X值，但是tableB.attributeB却没有X值。如果直接使用inner join, 含X值的元组就会被丢弃。如果我们想在结果中保留tableA.attributeA的X类似的元组,拿上面的例子来说如果我想保留tableA中的1 1元组。那么就是用类似于下面的SQL: select * from tableA left join tableB on tableA.attributeA = tableB.attributeB 等价于 select * from tableB right join tableA on tableA.attributeA = tableB.attributeB, left join 是保留左边表的元组，right join是保留右边表的元组。

最终结果为：

tableA.id attributeA  tableB.id  attributeB

    1             1             null            null

    2             2               1               2

---------------------------------------------------------------------------------------------------------------------------------------------

full join 全连接（或者为full outer join），left/right join只能保留一边的表元组，full join可以保留两边表的元组。select * from tableA full join tableB on tableA.attributeA = tableB.attributeB

最终结果为：

tableA.id attributeA  tableB.id  attributeB

    1             1             null            null

    2             2               1               2

    null         null            3                3

 

---------------------------------------------------------------------------------------------------------------------------------------------

cross join笛卡尔积，这个就是每一个A中元组和每个B中元组拼接成新的元组。

最终结果为：

tableA.id attributeA  tableB.id  attributeB

     1           1                 1            2

     1           1                 3            3

     2           2                 1            2

     2           2                 3            3

 

---------------------------------------------------------------------------------------------------------------------------------------------

natural join自然连接。这个是将两个表的公共属性做连接，同名公共属性只保留一份在最终结果里。例如tableA和tableB中都有id字段，自然连接后，结果里只保留一个id字段。例如select * from tableA natural join tableB

最终结果为：

id attributeA   attributeB

1           1            2

 

---------------------------------------------------------------------------------------------------------------------------------------------

半连接。貌似没有半连接对应的SQL关键字。半连接就是保留一张表参与连接的元组。所以我们可以再连接之后加入字段的选择。例如select attributeA from tableA join tableB on tableA.attributeA = tableB.attributeB.

最终结果为：

id attributeA

2       2

 

---------------------------------------------------------------------------------------------------------------------------------------------

自连接，这个名字我不记得在哪里见过。不过传说是有的。和其他join操作不一样之处在于自连接要加入distinct关键字，所以SQL类似于select distinct attributeA from tableA cross join tableB

最终结果为：

attributeA

     1

     2