以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=#