各种JOIN的理解

以A表和B表的连接而言：A.col=B.col

JOIN_INNER: 就是等值连接。找出A中有，B中也有，A和B的对应字段相等的记录的信息。

postgres=# select * from sales s inner join customers c on s.cust_id = c.cust_id;
 cust_id |   item   | cust_id | cust_name 
---------+----------+---------+-----------
       2 | camera   |       2 | John Doe
       3 | computer |       3 | Jane Doe
       3 | monitor  |       3 | Jane Doe
(3 rows)

postgres=# 

对于那种cross join，在PostgreSQL的代码内部，是作了两次JOIN_INNER:

postgres=# select * from sales s cross join customers c;
 cust_id |   item   | cust_id | cust_name 
---------+----------+---------+-----------
       2 | camera   |       1 | craig
       2 | camera   |       2 | John Doe
       2 | camera   |       3 | Jane Doe
       3 | computer |       1 | craig
       3 | computer |       2 | John Doe
       3 | computer |       3 | Jane Doe
       3 | monitor  |       1 | craig
       3 | monitor  |       2 | John Doe
       3 | monitor  |       3 | Jane Doe
       4 | printer  |       1 | craig
       4 | printer  |       2 | John Doe
       4 | printer  |       3 | Jane Doe
(12 rows)

postgres=# 

JOIN_LEFT: 就是A表优先。

postgres=# select * from sales s left outer join customers c on s.cust_id = c.cust_id;
 cust_id |   item   | cust_id | cust_name 
---------+----------+---------+-----------
       2 | camera   |       2 | John Doe
       3 | computer |       3 | Jane Doe
       3 | monitor  |       3 | Jane Doe
       4 | printer  |         | 
(4 rows)

postgres=# 

其实，即便是 Right outer Join，在PostgreSQL的源代码内部，也归类到 JOIN_LEFT里，
只不过是把A表和B表交换顺序而已。

postgres=# select * from sales s right outer join customers c on s.cust_id = c.cust_id;
 cust_id |   item   | cust_id | cust_name 
---------+----------+---------+-----------
         |          |       1 | craig
       2 | camera   |       2 | John Doe
       3 | computer |       3 | Jane Doe
       3 | monitor  |       3 | Jane Doe
(4 rows)

postgres=# 

 

JOIN_FULL: 就是把A表和B表中所有的数据都掏出来。

postgres=# select * from sales s full outer join customers c on s.cust_id = c.cust_id;
 cust_id |   item   | cust_id | cust_name 
---------+----------+---------+-----------
         |          |       1 | craig
       2 | camera   |       2 | John Doe
       3 | computer |       3 | Jane Doe
       3 | monitor  |       3 | Jane Doe
       4 | printer  |         | 
(5 rows)

JOIN_SEMI:
从PostgreSQL来说，是8.4后开始的，是对EXISTS作变换。

postgres=# select * from customers c where exists ( select * from sales s where s.cust_id = c.cust_id);
 cust_id | cust_name 
---------+-----------
       2 | John Doe
       3 | Jane Doe
(2 rows)

postgres=# 

JOIN_ANTI:
这个是对 NOT EXISTS作处理：

postgres=# select * from customers c where not exists ( select * from sales s where s.cust_id = c.cust_id);
 cust_id | cust_name 
---------+-----------
       1 | craig
(1 row)

postgres=#