数据库基础：SQL join 语句

一：概念

关系数据库可以在读取表的时候对表进行联接。
联接本质上是以某种方式联接两个独立的表，并返回一张结果表。

二：join用法

 tableA        tableB
 id name       id  name
 -- ----       --  ----
 1  Pirate     1   Rutabaga
 2  Monkey     2   Pirate
 3  Ninja      3   Darth Vader
 4  Spaghetti  4   Ninja

1. Cross join 交叉联接
   交叉联接通过from字句定义了一个在该字句中所列出关系上的笛卡尔积：
   第一个关系中的每个元组与第二个关系中的所有元组都进行连接。

   select * from tablea，tableb;
   select * from tablea cross join tableb;
   
   id |   name    | id |   name   
   ----+-----------+----+----------
    1  | Pirate    | 1  | Rutabaga
    1  | Pirate    | 2  | Pirate
    1  | Pirate    | 3  | Datch
    1  | Pirate    | 4  | Ninja
    2  | Monkey    | 1  | Rutabaga
    2  | Monkey    | 2  | Pirate
    2  | Monkey    | 3  | Datch
    2  | Monkey    | 4  | Ninja
    3  | Ninja     | 1  | Rutabaga
    3  | Ninja     | 2  | Pirate
    3  | Ninja     | 3  | Datch
    3  | Ninja     | 4  | Ninja
    4  | Spaghetti | 1  | Rutabaga
    4  | Spaghetti | 2  | Pirate
    4  | Spaghetti | 3  | Datch
    4  | Spaghetti | 4  | Ninja
   

2. （Inner可选）join 内连接

   tableA         tableB            tableC
   id name        id  name          id  words
   -- ----        --  ----          --  ----
   1  Pirate      1   Rutabaga      1   Rutabaga
   2  Monkey      2   Pirate        2   Pirate
   3  Ninja       3   Darth Vader   3   Darth Vader
   4  Spaghetti   4   Ninja         4   Ninja
   

   2.1 连接条件 natural join
   自然连接运算作用于两个关系，并产生一个关系作为结果。
   不同于两个关系上的笛卡尔积，它将第一个关系的每个元组与第二个关系的所有元组都进行连接；
   自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

   select * from tableA natural join tableC;
    id |   name    |  words   
   ----+-----------+----------
     1 | Pirate    | Rutabaga
     2 | Monkey    | Pirate
     3 | Ninja     | Datch
     4 | Spaghetti | Ninja
   (4 rows)
   

   PS:
   （1）结果中在两个关系中都出现的属性不会重复列出，这样的属性只出现一次。
   列出属性的顺序：先是两个关系模式中都出现的属性，然后是只出现在第一个关系模式中的属性，最后是只出现在第二个关系模式中的属性。
   （2）自然连接的危险在于如果两个关系模式中有多个同名属性，需要全部匹配。

   select * from tableA natural join tableB;
    id | name 
   ----+------
   (0 rows)
   

   2.2 连接条件 join ... using(A1,A2,A3)
   SQL提供了一种自然连接的构造形式，这样用户来指定需要哪些列相等，而不需要同名属性的取值全部相等。

   postgres=# select * from tableA join tableB using(id);
    id |   name    |   name   
   ----+-----------+----------
     1 | Pirate    | Rutabaga
     2 | Monkey    | Pirate
     3 | Ninja     | Datch
     4 | Spaghetti | Ninja
   (4 rows)
   

   PS: join ... using(A1,A2,A3) 需要给定一个属性名列表，其两个输入中都必须有指定名称的属性。
   2.3 连接条件 join ... on<predicate>
   SQL支持另一种形式的连接条件：join ... on<predicate>，相比 join ... using(A1,A2,A3) 可以指定任意的连接条件，更为灵活。

   select * from tableA join tableB on tableA.id=tableB.id;
    id |   name    | id |   name   
   ----+-----------+----+----------
     1 | Pirate    |  1 | Rutabaga
     2 | Monkey    |  2 | Pirate
     3 | Ninja     |  3 | Datch
     4 | Spaghetti |  4 | Ninja
   (4 rows)
   
   select * from tableA join tableB on tableA.id<tableB.id;
    id |  name  | id |  name  
   ----+--------+----+--------
     1 | Pirate |  2 | Pirate
     1 | Pirate |  3 | Datch
     1 | Pirate |  4 | Ninja
     2 | Monkey |  3 | Datch
     2 | Monkey |  4 | Ninja
     3 | Ninja  |  4 | Ninja
   (6 rows)
   

   PS: join ... on<predicate> 的结果中会重复出现两个关系中相同的属性。

3. outer join 外连接
   在内连接中，参与连接的任何一个关系或者两个关系中的某些元组可能会丢失。
   例如：同名属性中，有一个关系的属性值为null。
   外连接运算与内连接相似，但是会在结果中创建包含空值元组的方式，保留了那些在内连接中丢失的元组。
   外连接包含三种形式：

   • 左外连接left outer join,只保留出现在左边的关系中的元组；
   • 右外连接right outer join，只保留出现在右边的关系中的元组；
   • 全外连接full outer join，保留出现在两个关系中的元组；

    tableA         tableB          
    id name        id  name        
    -- ----        --  ----          
    1  Pirate      1   Rutabaga      
    3  null        2   null        
   

   select * from tableA left outer join tableB using(id);
    id |  name  |   name   
   ----+--------+----------
     1 | Pirate | Rutabaga
     3 |        | 
   (2 rows)
   select * from tableA right outer join tableB using(id);
    id |  name  |   name   
   ----+--------+----------
     1 | Pirate | Rutabaga
     2 |        | 
   (2 rows)
   select * from tableA full outer join tableB using(id);
    id |  name  |   name   
   ----+--------+----------
     1 | Pirate | Rutabaga
     3 |        | 
     2 |        | 
   (3 rows)
   

   注意: outer join 外连接也可以像内连接那样与任意的连接条件（自然连接、using条件或on条件）进行组合。

三：总结

1. 连接类型（内/外连接）可以和连接条件自由组合。

   
   

2. 注意不同数据库的实现。mysql

   select * from a natural join b ; 相同属性名，相同属性值
   select * from a cross JOIN b ; select * from a JOIN b ;select * from a FULL JOIN b ;  交叉连接效果
   
   select * from a  JOIN b + 连接条件on/using; 
   select * from a LEFT/RIGHT  JOIN b + 连接条件on/using; 
   

3. 韦恩图解释 SQL join 语句
   

   

---

[2016.10.12]