用对象关系去思考 LEFT JOIN 的用法

有这样两个表－－ custom 和 address 表。

对于每个customer, 有0个到多个地址。对于地址，本来是一定有customer的，但是这里加入一个非法的地址。address用外键cid关联到customer。

这个语句：select custom.*, address.address from custom left join address on address.cid = custom.cid;

相当于找出所有的customer, 然后对应找出每个customer的address－－select了customer以后，然后把address乘过来。

select custom.*, address.address from custom left join address on address.cid = custom.cid;
1|custom1|addr1 of c1
1|custom1|addr2 of c1
2|custom2|
3|custom3|addr1 of c3
3|custom3|addr2 of c3
4|custom4|addr1 of c4

而相反的结果是：

sqlite> select custom.*, address.address from address left join custom on address.cid = custom.cid;
1|custom1|addr1 of c1
1|custom1|addr2 of c1
3|custom3|addr1 of c3
3|custom3|addr2 of c3
4|custom4|addr1 of c4
||missing custom, invalid address

则是对于每个地址，去找customer。

测试时候所用的所有数据如下：

sqlite> .schema custom
CREATE TABLE custom( cid int,
cname varchar(32));

sqlite> select * from custom;
1|custom1
2|custom2
3|custom3
4|custom4

sqlite> .schema address
CREATE TABLE address(aid int, cid int, address text); - - sqlite保留了我创建时候的格式..

sqlite> select * from address;
1|1|addr1 of c1
2|1|addr2 of c1
3|3|addr1 of c3
4|3|addr2 of c3
5|4|addr1 of c4
6|0|missing custom, invalid address

后记：这个测试的动机和部分内容来源于SQLObject开发文档。