Postgresql join 操作

1. 建表&插入数据

2. inner join

3. full outer join

4. left outer join

5. 参考

1. 建表&插入数据

创建表 test_a,test_b

shop_5>CREATETABLEtest_a(idserial,nameVARCHAR(32));
CREATETABLE
Time:0.019s

shop_5>\dtest_a;
+----------+-----------------------+------------------------------------------------------+
|Column|Type|Modifiers|
|----------+-----------------------+------------------------------------------------------|
|id|integer|notnulldefaultnextval('test_a_id_seq'::regclass)|
|name|charactervarying(32)||
+----------+-----------------------+------------------------------------------------------+
Time:0.006s

shop_5>INSERTINTOtest_a(name)VALUES('a'),('b'),('c'),('d');
INSERT04
Time:0.011s

shop_5>SELECT*fromtest_a;
+------+--------+
|id|name|
|------+--------|
|1|a|
|2|b|
|3|c|
|4|d|
+------+--------+
SELECT4
Time:0.001s

shop_5>CREATETABLEtest_b(idserial,nameVARCHAR(32));
CREATETABLE
Time:0.012s

shop_5>\dtest_b;
+----------+-----------------------+------------------------------------------------------+
|Column|Type|Modifiers|
|----------+-----------------------+------------------------------------------------------|
|id|integer|notnulldefaultnextval('test_b_id_seq'::regclass)|
|name|charactervarying(32)||
+----------+-----------------------+------------------------------------------------------+
Time:0.003s

shop_5>INSERTINTOtest_b(name)VALUES('c'),('d'),('e'),('f');
INSERT04
Time:0.009s

shop_5>SELECT*fromtest_b;
+------+--------+
|id|name|
|------+--------|
|1|c|
|2|d|
|3|e|
|4|f|
+------+--------+
SELECT4
Time:0.002s

2. inner join

内联合（inner join）只生成同时匹配表A和表B的记录集。

shop_5>SELECT*fromtest_aaINNERJOINtest_bbona.name=b.name;
+------+--------+------+--------+
|id|name|id|name|
|------+--------+------+--------|
|3|c|1|c|
|4|d|2|d|
+------+--------+------+--------+
SELECT2
Time:0.001s

3. full outer join

全外联合（full outer join）生成表A和表B里的记录全集，包括两边都匹配的记录。如果有一边没有匹配的，缺失的这一边为null。

shop_5>SELECT*fromtest_aaFULLOUTERJOINtest_bbona.name=b.name;
+--------+--------+--------+--------+
|id|name|id|name|
|--------+--------+--------+--------|
|1|a|<null>|<null>|
|2|b|<null>|<null>|
|3|c|1|c|
|4|d|2|d|
|<null>|<null>|3|e|
|<null>|<null>|4|f|
+--------+--------+--------+--------+
SELECT6
Time:0.001s

4. left outer join

左外联合（left outer join）生成表A的所有记录，包括在表B里匹配的记录。如果没有匹配的，右边将是null。

shop_5>SELECT*fromtest_aaLEFTOUTERJOINtest_bbona.name=b.name;
+------+--------+--------+--------+
|id|name|id|name|
|------+--------+--------+--------|
|1|a|<null>|<null>|
|2|b|<null>|<null>|
|3|c|1|c|
|4|d|2|d|
+------+--------+--------+--------+
SELECT4
Time:0.003s

5. 参考

http://francs3.blog.163.com/blog/static/405767272012101011193243/

http://www.yiibai.com/html/postgresql/2013/080569.html

http://blog.jobbole.com/40443/