SQLite3中的三种Join方式

前一阵子设计数据库结构，有个设计是要保存所有的音、视频和图片信息的metadata到表中，并要求可以不分类型的一次全部检索出来。原来有个设计是设计一张大表，把所有的字段不分类型全都添进去，通过类型字段进行判断，读取哪些metadata作为检索结果。这样就导致表的结构十分复杂，且大量的字段为空。为了解决这个问题，查了一下SQL的Join算法，解决了这个设计问题。以下是SQLite3中支持的Join方式及使用说明：

1. Cross Join

又叫笛卡尔积，匹配前一个表与后一个表的每一行和每一列，这样得到的结果集为n*m行（n, m分别为每张表的行数），x+y列（x, y分别为每张表的列数）。可见，该结果集可能会成为一个巨大的表，对内存和后续处理都会造成巨大压力，所以，慎用（真没用过）。

语法：SELECT ... FROM t1 CROSS JOIN t2 ...

（盗用Using SQLite3 2010中的图）

2. Inner Join

类似Cross Join，但内建机制限制了返回的结果数量。返回的结果集不会超过x + y列，行数在0- n*m行之间。有3种方法用来指定Inner Join的判断条件：

第一种是On表达式：SELECT ... FROM t1 JOIN t2 ON conditional_expression ...，例如：SELECT ... FROM employee JOIN resource ON employee.eid = resource.eid ...。

但On这种方式有俩个问题：一是语句比较长，二是存在重复列，如俩个eid。因此，可以使用第二种方式Using表达式：SELECT ... FROM t1 JOIN t2 USING ( col1 ,... ) ...，这种Join返回的结果集中没有重复的字段，只是每个字段必须存在于各个表中。

更简洁的方式是，使用第三种方式Natural Join：SQL自动检测各表中每一列是否匹配，这样，即使表结构发生变化，也不用修改SQL语句，可以自动适应变化。

（继续盗图）

3. Outer Join

解决我的问题使用的是Outer Join。

SQLite3只支持left outer join，其结果集由不大于x + y列，n - n*m行构成，至少包含左侧表的每一行，对于Join后不存在的字段值，则赋NULL。这样得到的表与我们之前设计那个全集结果一样，但数据结构更清晰，空间占用更少。

（最后一次盗图）