SQLite3中的三种Join方式

汉王 2016年11月09日 笔记 收藏

前一阵子设计数据库结构,有个设计是要保存所有的音、视频和图片信息的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。这样得到的表与我们之前设计那个全集结果一样,但数据结构更清晰,空间占用更少。

(最后一次盗图)