sqlite中对于join的实现
一、多表操作
这个问题并不是一个空穴来风的纯粹分析，应用的场景是假设一个数据库中相同的表格内容是按照每个月创建一个table，这样的好处在于随着日期的变化，我们可以通过一个简单的drop命令删除不再需要的数据，而且表的大小也不会太膨胀。这些好处的作用非常明显，但是也有一些不太方便的地方，假设我们想知道过去一段时间内所有商品的最高出售量在哪一天，这个时候处理就比较麻烦，因为此时可能涉及到两张表的操作，而对于最值的计算必须同时考虑到这两者表的内容。
这个的说明还是有些抽象，不过在选择极值作者不但给出了解决的各种方法，同时也叙述的比较清晰明了，所以有兴趣了解问题起因的同学可以看下那个文章。对于join的一些形象而直观的图形化表示可以在下面的地址中找到join示意图。
不过好在这里并不是说明这些问题的解决方法，前任之述备矣，这里还是看一下sqlite对于一个jion的查询步骤。柿子挑软的捏，mysql虽然华丽，但是并不是很容易入手，而且它的explain功能并没有像sqlite有一个完备的虚拟机系统，由于sqlite属于麻雀虽小，五脏俱全的一个系统，所以从这个地方看下数据库的实现并无不妥，就像linus当时做linux内核的时候还是从MINIX这样一个教学型的操作系统开始。
下面的数据库结构和最开始的极值选择中给出的例子类似：
sqlite> .schema fruits
CREATE TABLE fruits(type varchar(64), variety varchar(64), price float);
sqlite>
二、inner join
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type ) as x inner join fruits as f on f.price = x.minprice;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 OpenEphemeral 0 2 0 00 打开临时表0，每个record 2列
2 OpenEphemeral 3 3 0 keyinfo(1,BINARY) 00 临时表3，每个record 3列，key值为二进制结构
3 Integer 0 5 0 00 clear abort flag
4 Integer 0 4 0 00 indicate accumulator empty
5 Gosub 7 44 0 00 清零1……5五个寄存器的值
6 Goto 0 64 0 00 transaction初始化
7 OpenRead 1 3 0 3 00 fruits 打开fruits表
8 Rewind 1 16 0 00
9 Column 1 0 10 00 fruits.type
10 Sequence 3 11 0 00
11 Column 1 2 12 00 fruits.price
12 RealAffinity 12 0 0 00
13 MakeRecord 10 3 13 00
14 IdxInsert 3 13 0 00
15 Next 1 9 0 01
16 Close 1 0 0 00 以上操作将fruit表中type、price以及动态生成的sequence组成一个新的临时数据库，游标放入编号3中。这里对应子查询中的select type, min(price) ，组成了临时数据表，但是没有执行聚合(aggregate)的min操作。
17 Sort 3 48 0 00 GROUP BY sort
18 Column 3 0 9 00
19 Compare 8 9 1 keyinfo(1,BINARY) 00
20 Jump 21 25 21 00
21 Move 9 8 1 00
22 Gosub 6 35 0 00 output one row
23 IfPos 5 48 0 00 check abort flag
24 Gosub 7 44 0 00 reset accumulator
25 Column 3 2 10 00
26 CollSeq 0 0 0 collseq(BINARY) 00
27 AggStep 0 10 2 min(1) 01
28 Column 3 0 1 00
29 Integer 1 4 0 00 indicate data in accumulator
30 Next 3 18 0 00 如果该临时表中没有记录，则顺序顺序执行，跳转到48地址。
31 Gosub 6 35 0 00 output final row
32 Goto 0 48 0 00 这个整个就是对于min导致的group操作，之前一篇日志中已经说明了这个实现思路，这里不再赘述了。
33 Integer 1 5 0 00 set abort flag
34 Return 6 0 0 00
35 IfPos 4 37 0 00 Groupby result generator entry point
36 Return 6 0 0 00
37 AggFinal 2 1 0 min(1) 00
38 SCopy 1 14 0 00
39 SCopy 2 15 0 00
40 MakeRecord 14 2 13 00
41 NewRowid 0 16 0 00
42 Insert 0 13 16 08
43 Return 6 0 0 00 end groupby result generator
44 Null 0 1 0 00
45 Null 0 3 0 00
46 Null 0 2 0 00
47 Return 7 0 0 00
48 OpenRead 2 3 0 3 00 fruits 再次打开fruit表
49 Rewind 0 62 0 00 重新打开之前子select中创建的临时表，这里大家可以看一下这里二重循环的外层循环和内层循环分别对应哪张表。
50 Rewind 2 61 0 00 重新打开fruits表，这里是一个跳转点，之后的next会跳回到这里。
51 Column 2 2 13 00 fruits.price 接下来就是对于where中条件的判断，价格相等
52 RealAffinity 13 0 0 00
53 Column 0 1 16 00 sqlite_subquery_9BA5868_.minprice
54 Ne 16 60 13 collseq(BINARY) 6b
55 Column 2 0 17 00 fruits.type
56 Column 2 1 18 00 fruits.variety
57 Column 2 2 19 00 fruits.price
58 RealAffinity 19 0 0 00
59 ResultRow 17 3 0 00 组成一个新record
60 Next 2 51 0 01 如果fruits表中还有元素，则继续fruits中下一条，否则继续select子表下一条。
61 Next 0 50 0 01 如果子select生成表中还有元素，则子select继续下一条。这里要注意它的前向跳转点是50，在50地址处重新归零了fruits表。这也就是说，对于select子表的每一条记录，都需要遍历fruits表的所有记录。
62 Close 2 0 0 00
63 Halt 0 0 0 00
64 Transaction 0 0 0 00
65 VerifyCookie 0 2 0 00
66 TableLock 0 3 0 fruits 00
67 Goto 0 7 0 00
sqlite>
三、left/right join
在codeproject以及大部分关于left join的例子中，都会展示两个集合取不同部分的操作，例如在A中但是不在B中的元素这种例子，而使用的方法一般是R.column=null来判断不在R中出现的条件，但是从上面的例子中可以看到，这个inner join是没有位置来处理这个连接的，所以这个判断在inner中没有道理的，所以单独看下对于单侧join的实现。
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type ) as x left join fruits as f on f.price = x.minprice and x.minprice=null;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 OpenEphemeral 0 2 0 00
2 OpenEphemeral 3 3 0 keyinfo(1,BINARY) 00
3 Integer 0 5 0 00 clear abort flag
4 Integer 0 4 0 00 indicate accumulator empty
5 Gosub 7 44 0 00
6 Goto 0 71 0 00
7 OpenRead 1 3 0 3 00 fruits
8 Rewind 1 16 0 00
9 Column 1 0 10 00 fruits.type
10 Sequence 3 11 0 00
11 Column 1 2 12 00 fruits.price
12 RealAffinity 12 0 0 00
13 MakeRecord 10 3 13 00
14 IdxInsert 3 13 0 00
15 Next 1 9 0 01
16 Close 1 0 0 00
17 Sort 3 48 0 00 GROUP BY sort
18 Column 3 0 9 00
19 Compare 8 9 1 keyinfo(1,BINARY) 00
20 Jump 21 25 21 00
21 Move 9 8 1 00
22 Gosub 6 35 0 00 output one row
23 IfPos 5 48 0 00 check abort flag
24 Gosub 7 44 0 00 reset accumulator
25 Column 3 2 10 00
26 CollSeq 0 0 0 collseq(BINARY) 00
27 AggStep 0 10 2 min(1) 01
28 Column 3 0 1 00
29 Integer 1 4 0 00 indicate data in accumulator
30 Next 3 18 0 00
31 Gosub 6 35 0 00 output final row
32 Goto 0 48 0 00
33 Integer 1 5 0 00 set abort flag
34 Return 6 0 0 00
35 IfPos 4 37 0 00 Groupby result generator entry point
36 Return 6 0 0 00
37 AggFinal 2 1 0 min(1) 00
38 SCopy 1 14 0 00
39 SCopy 2 15 0 00
40 MakeRecord 14 2 13 00
41 NewRowid 0 16 0 00
42 Insert 0 13 16 08
43 Return 6 0 0 00 end groupby result generator
44 Null 0 1 0 00
45 Null 0 3 0 00
46 Null 0 2 0 00
47 Return 7 0 0 00
48 OpenRead 2 3 0 3 00 fruits
49 Rewind 0 69 0 00
50 Integer 0 17 0 00 init LEFT JOIN no-match flag 清除命中标志
51 Rewind 2 65 0 00
52 Column 2 2 13 00 fruits.price
53 RealAffinity 13 0 0 00
54 Column 0 1 16 00 sqlite_subquery_9BA6CB8_.minprice
55 Ne 16 64 13 collseq(BINARY) 6b
56 Null 0 18 0 00
57 Ne 18 64 16 collseq(BINARY) 6a
58 Integer 1 17 0 00 record LEFT JOIN hit
59 Column 2 0 19 00 fruits.type
60 Column 2 1 20 00 fruits.variety
61 Column 2 2 21 00 fruits.price
62 RealAffinity 21 0 0 00
63 ResultRow 19 3 0 00
64 Next 2 52 0 01 这里同样是对第二个表fruits作为内层循环，外层是子select生成的临时表
65 IfPos 17 68 0 00 If the value of register P1 is 1 or greater, jump to P2.这里是inner join所没有的一个操作，对于子select返回的一条记录，如果此时在整个fruits中没有对应项，则此时sqlite会为该join生成一个NullRow暨龙乡，而NullRow的意义则是 Move the cursor P1 to a null row. Any OP_Column operations hat occur while the cursor is on the null row will always write a NULL，所以之后游标虽然没有匹配，但是此时有一个逻辑上的匹配列，就好象零的意义一样，存在的本身就表示了不存在。
66 NullRow 2 0 0 00 .
67 Goto 0 58 0 00
68 Next 0 50 0 01
69 Close 2 0 0 00
70 Halt 0 0 0 00
71 Transaction 0 0 0 00
72 VerifyCookie 0 2 0 00
73 TableLock 0 3 0 fruits 00
74 Goto 0 7 0 00
sqlite>
四、回归一下问题
对于开始说的两表合并的问题，选择最大记录项的方法依然需要使用这里所说的join方法，但是还需要使用union all首先将两张表合成一张表，这个表可以作为一个子表，或者作为一个mysql中支持的temporary table，临时表的优点在于它的生命期只在一个session中，当会话结束之后，临时表会被自动删除。
这个问题并不是一个空穴来风的纯粹分析，应用的场景是假设一个数据库中相同的表格内容是按照每个月创建一个table，这样的好处在于随着日期的变化，我们可以通过一个简单的drop命令删除不再需要的数据，而且表的大小也不会太膨胀。这些好处的作用非常明显，但是也有一些不太方便的地方，假设我们想知道过去一段时间内所有商品的最高出售量在哪一天，这个时候处理就比较麻烦，因为此时可能涉及到两张表的操作，而对于最值的计算必须同时考虑到这两者表的内容。
这个的说明还是有些抽象，不过在选择极值作者不但给出了解决的各种方法，同时也叙述的比较清晰明了，所以有兴趣了解问题起因的同学可以看下那个文章。对于join的一些形象而直观的图形化表示可以在下面的地址中找到join示意图。
不过好在这里并不是说明这些问题的解决方法，前任之述备矣，这里还是看一下sqlite对于一个jion的查询步骤。柿子挑软的捏，mysql虽然华丽，但是并不是很容易入手，而且它的explain功能并没有像sqlite有一个完备的虚拟机系统，由于sqlite属于麻雀虽小，五脏俱全的一个系统，所以从这个地方看下数据库的实现并无不妥，就像linus当时做linux内核的时候还是从MINIX这样一个教学型的操作系统开始。
下面的数据库结构和最开始的极值选择中给出的例子类似：
sqlite> .schema fruits
CREATE TABLE fruits(type varchar(64), variety varchar(64), price float);
sqlite>
二、inner join
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type ) as x inner join fruits as f on f.price = x.minprice;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 OpenEphemeral 0 2 0 00 打开临时表0，每个record 2列
2 OpenEphemeral 3 3 0 keyinfo(1,BINARY) 00 临时表3，每个record 3列，key值为二进制结构
3 Integer 0 5 0 00 clear abort flag
4 Integer 0 4 0 00 indicate accumulator empty
5 Gosub 7 44 0 00 清零1……5五个寄存器的值
6 Goto 0 64 0 00 transaction初始化
7 OpenRead 1 3 0 3 00 fruits 打开fruits表
8 Rewind 1 16 0 00
9 Column 1 0 10 00 fruits.type
10 Sequence 3 11 0 00
11 Column 1 2 12 00 fruits.price
12 RealAffinity 12 0 0 00
13 MakeRecord 10 3 13 00
14 IdxInsert 3 13 0 00
15 Next 1 9 0 01
16 Close 1 0 0 00 以上操作将fruit表中type、price以及动态生成的sequence组成一个新的临时数据库，游标放入编号3中。这里对应子查询中的select type, min(price) ，组成了临时数据表，但是没有执行聚合(aggregate)的min操作。
17 Sort 3 48 0 00 GROUP BY sort
18 Column 3 0 9 00
19 Compare 8 9 1 keyinfo(1,BINARY) 00
20 Jump 21 25 21 00
21 Move 9 8 1 00
22 Gosub 6 35 0 00 output one row
23 IfPos 5 48 0 00 check abort flag
24 Gosub 7 44 0 00 reset accumulator
25 Column 3 2 10 00
26 CollSeq 0 0 0 collseq(BINARY) 00
27 AggStep 0 10 2 min(1) 01
28 Column 3 0 1 00
29 Integer 1 4 0 00 indicate data in accumulator
30 Next 3 18 0 00 如果该临时表中没有记录，则顺序顺序执行，跳转到48地址。
31 Gosub 6 35 0 00 output final row
32 Goto 0 48 0 00 这个整个就是对于min导致的group操作，之前一篇日志中已经说明了这个实现思路，这里不再赘述了。
33 Integer 1 5 0 00 set abort flag
34 Return 6 0 0 00
35 IfPos 4 37 0 00 Groupby result generator entry point
36 Return 6 0 0 00
37 AggFinal 2 1 0 min(1) 00
38 SCopy 1 14 0 00
39 SCopy 2 15 0 00
40 MakeRecord 14 2 13 00
41 NewRowid 0 16 0 00
42 Insert 0 13 16 08
43 Return 6 0 0 00 end groupby result generator
44 Null 0 1 0 00
45 Null 0 3 0 00
46 Null 0 2 0 00
47 Return 7 0 0 00
48 OpenRead 2 3 0 3 00 fruits 再次打开fruit表
49 Rewind 0 62 0 00 重新打开之前子select中创建的临时表，这里大家可以看一下这里二重循环的外层循环和内层循环分别对应哪张表。
50 Rewind 2 61 0 00 重新打开fruits表，这里是一个跳转点，之后的next会跳回到这里。
51 Column 2 2 13 00 fruits.price 接下来就是对于where中条件的判断，价格相等
52 RealAffinity 13 0 0 00
53 Column 0 1 16 00 sqlite_subquery_9BA5868_.minprice
54 Ne 16 60 13 collseq(BINARY) 6b
55 Column 2 0 17 00 fruits.type
56 Column 2 1 18 00 fruits.variety
57 Column 2 2 19 00 fruits.price
58 RealAffinity 19 0 0 00
59 ResultRow 17 3 0 00 组成一个新record
60 Next 2 51 0 01 如果fruits表中还有元素，则继续fruits中下一条，否则继续select子表下一条。
61 Next 0 50 0 01 如果子select生成表中还有元素，则子select继续下一条。这里要注意它的前向跳转点是50，在50地址处重新归零了fruits表。这也就是说，对于select子表的每一条记录，都需要遍历fruits表的所有记录。
62 Close 2 0 0 00
63 Halt 0 0 0 00
64 Transaction 0 0 0 00
65 VerifyCookie 0 2 0 00
66 TableLock 0 3 0 fruits 00
67 Goto 0 7 0 00
sqlite>
三、left/right join
在codeproject以及大部分关于left join的例子中，都会展示两个集合取不同部分的操作，例如在A中但是不在B中的元素这种例子，而使用的方法一般是R.column=null来判断不在R中出现的条件，但是从上面的例子中可以看到，这个inner join是没有位置来处理这个连接的，所以这个判断在inner中没有道理的，所以单独看下对于单侧join的实现。
sqlite> explain select f.type, f.variety, f.price from (select type, min(price) as minprice from fruits group by type ) as x left join fruits as f on f.price = x.minprice and x.minprice=null;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 OpenEphemeral 0 2 0 00
2 OpenEphemeral 3 3 0 keyinfo(1,BINARY) 00
3 Integer 0 5 0 00 clear abort flag
4 Integer 0 4 0 00 indicate accumulator empty
5 Gosub 7 44 0 00
6 Goto 0 71 0 00
7 OpenRead 1 3 0 3 00 fruits
8 Rewind 1 16 0 00
9 Column 1 0 10 00 fruits.type
10 Sequence 3 11 0 00
11 Column 1 2 12 00 fruits.price
12 RealAffinity 12 0 0 00
13 MakeRecord 10 3 13 00
14 IdxInsert 3 13 0 00
15 Next 1 9 0 01
16 Close 1 0 0 00
17 Sort 3 48 0 00 GROUP BY sort
18 Column 3 0 9 00
19 Compare 8 9 1 keyinfo(1,BINARY) 00
20 Jump 21 25 21 00
21 Move 9 8 1 00
22 Gosub 6 35 0 00 output one row
23 IfPos 5 48 0 00 check abort flag
24 Gosub 7 44 0 00 reset accumulator
25 Column 3 2 10 00
26 CollSeq 0 0 0 collseq(BINARY) 00
27 AggStep 0 10 2 min(1) 01
28 Column 3 0 1 00
29 Integer 1 4 0 00 indicate data in accumulator
30 Next 3 18 0 00
31 Gosub 6 35 0 00 output final row
32 Goto 0 48 0 00
33 Integer 1 5 0 00 set abort flag
34 Return 6 0 0 00
35 IfPos 4 37 0 00 Groupby result generator entry point
36 Return 6 0 0 00
37 AggFinal 2 1 0 min(1) 00
38 SCopy 1 14 0 00
39 SCopy 2 15 0 00
40 MakeRecord 14 2 13 00
41 NewRowid 0 16 0 00
42 Insert 0 13 16 08
43 Return 6 0 0 00 end groupby result generator
44 Null 0 1 0 00
45 Null 0 3 0 00
46 Null 0 2 0 00
47 Return 7 0 0 00
48 OpenRead 2 3 0 3 00 fruits
49 Rewind 0 69 0 00
50 Integer 0 17 0 00 init LEFT JOIN no-match flag 清除命中标志
51 Rewind 2 65 0 00
52 Column 2 2 13 00 fruits.price
53 RealAffinity 13 0 0 00
54 Column 0 1 16 00 sqlite_subquery_9BA6CB8_.minprice
55 Ne 16 64 13 collseq(BINARY) 6b
56 Null 0 18 0 00
57 Ne 18 64 16 collseq(BINARY) 6a
58 Integer 1 17 0 00 record LEFT JOIN hit
59 Column 2 0 19 00 fruits.type
60 Column 2 1 20 00 fruits.variety
61 Column 2 2 21 00 fruits.price
62 RealAffinity 21 0 0 00
63 ResultRow 19 3 0 00
64 Next 2 52 0 01 这里同样是对第二个表fruits作为内层循环，外层是子select生成的临时表
65 IfPos 17 68 0 00 If the value of register P1 is 1 or greater, jump to P2.这里是inner join所没有的一个操作，对于子select返回的一条记录，如果此时在整个fruits中没有对应项，则此时sqlite会为该join生成一个NullRow暨龙乡，而NullRow的意义则是 Move the cursor P1 to a null row. Any OP_Column operations hat occur while the cursor is on the null row will always write a NULL，所以之后游标虽然没有匹配，但是此时有一个逻辑上的匹配列，就好象零的意义一样，存在的本身就表示了不存在。
66 NullRow 2 0 0 00 .
67 Goto 0 58 0 00
68 Next 0 50 0 01
69 Close 2 0 0 00
70 Halt 0 0 0 00
71 Transaction 0 0 0 00
72 VerifyCookie 0 2 0 00
73 TableLock 0 3 0 fruits 00
74 Goto 0 7 0 00
sqlite>
四、回归一下问题
对于开始说的两表合并的问题，选择最大记录项的方法依然需要使用这里所说的join方法，但是还需要使用union all首先将两张表合成一张表，这个表可以作为一个子表，或者作为一个mysql中支持的temporary table，临时表的优点在于它的生命期只在一个session中，当会话结束之后，临时表会被自动删除。
