SQL join 连接

连接JOIN

  T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
  T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
  T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

    INNER 和 OUTER 对所有连接类型都是可选的。INNER 是缺省； LEFT、RIGHT、和 FULL 隐含外连接。
   
    连接条件 在 ON 或 USING 子句里声明，或者用关键字 NATURAL 隐含地声明。
   
    ON 是最常见的连接条件的类型：分别来自T1和T2的行在ON表达式上运算结果为真，就算是匹配的行。

    USING 是缩写：接收一个用逗号分隔的字段名字列表，字段必须是连接表共有的，字段对必须相同，输出时为每对字段输出一个字段。USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)，只不过使用了 USING 时 a,b,c 字段输出不是一对。
   
    NATURAL 是 USING 的缩写：形成一个 USING 列表，由那些在两个表里都出现了的字段名字组成。
   
   
  条件JOIN的可能类型：
   
    INNER JOIN
      内连接，T1 和 T2 中满足条件的行。
   
    LEFT OUTER JOIN
      执行一次内连接，然后为 T1 中那些和 T2 不满足连接条件的行返回一个连接行，该连接行中对应 T2 的列用空值补齐。因此，生成的连接表中无条件地包含来自 T1 中每一行至少一个副本。
   
    RIGHT OUTER JOIN
      与 LEFT OUTER JOIN 连接方向相反。
   
    FULL OUTER JOIN
      执行一次内连接．然后为 T1 中那些和 T2 不满足连接条件的行返回一个连接行，该连接行中对应 T2 的列用空值补齐。同样为 T2 中那些和 T1 不满足连接条件的行返回一个连接行，该连接行中对应 T1 的列用空值补齐。
   
    可以在JOIN子句周围使用圆括号来控制连接顺序。缺省的JOIN子句是从左向右嵌套的。
   
   
  为了解释这些问题，假设我们有一个表 t1
        num | name
        -----+------
           1 | a
           2 | b
           3 | c
    和 t2
        num | value
        -----+-------
           1 | xxx
           3 | yyy
           5 | zzz

    然后我们用不同的连接方式可以获得各种结果：

        => SELECT * FROM t1 CROSS JOIN t2;
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           1 | a    |   3 | yyy
           1 | a    |   5 | zzz
           2 | b    |   1 | xxx
           2 | b    |   3 | yyy
           2 | b    |   5 | zzz
           3 | c    |   1 | xxx
           3 | c    |   3 | yyy
           3 | c    |   5 | zzz
        (9 rows)

        => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           3 | c    |   3 | yyy
        (2 rows)

        => SELECT * FROM t1 INNER JOIN t2 USING (num);
         num | name | value
        -----+------+-------
           1 | a    | xxx
           3 | c    | yyy
        (2 rows)

        => SELECT * FROM t1 NATURAL INNER JOIN t2;
         num | name | value
        -----+------+-------
           1 | a    | xxx
           3 | c    | yyy
        (2 rows)

        => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           2 | b    |     |
           3 | c    |   3 | yyy
        (3 rows)

        => SELECT * FROM t1 LEFT JOIN t2 USING (num);
         num | name | value
        -----+------+-------
           1 | a    | xxx
           2 | b    |
           3 | c    | yyy
        (3 rows)

        => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           3 | c    |   3 | yyy
             |      |   5 | zzz
        (3 rows)

        => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           2 | b    |     |
           3 | c    |   3 | yyy
             |      |   5 | zzz
        (4 rows)
   
    用 ON 声明的连接条件也可以包含与连接不直接相关的条件。这种功能可能对某些查询很有用，但是需要我们仔细想想。比如：
   
        => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
         num | name | num | value
        -----+------+-----+-------
           1 | a    |   1 | xxx
           2 | b    |     |
           3 | c    |     |
        (3 rows)
   
   
  进行左连接，SQL语句有两种写法：
 
    SELECT * FROM T1,T2 WHERE T1.F1=T2.F1(+)
    SELECT * FROM T1 LEFT JOIN T2 ON T1.F1=T2.F1

    SQL> select * from a;
             A
    ----------
             1
             2
             3
             4
   
    SQL> select * from b;
             A          B          C
    ---------- ---------- ----------
             1          4         10
             2          5         11
             5          6         12
             6          7         13
   
    SQL>select A.a,B.A,B.b,b.c from A,B where A.a=B.a;
             A          A          B          C
    ---------- ---------- ---------- ----------
             1          1          4         10
             2          2          5         11
   
    SQL>select A.a,B.A,B.b,b.c from A,B where A.a=B.a(+);
             A          A          B          C
    ---------- ---------- ---------- ----------
             1          1          4         10
             2          2          5         11
             3                      
             4                      
   
    SQL>select A.a,B.A,B.b,b.c from A,B where A.a=B.a(+) and b.c>10;
            A          A          B          C
    ---------- ---------- ---------- ----------
             2          2          5         11
   
    SQL>select A.a,B.A,B.b,b.c from A,B where A.a=B.a(+) and b.c(+)>10;
   
             A          A          B          C
    ---------- ---------- ---------- ----------
             1                      
             2          2          5         11
             3                      
             4