Ownsight

?找回密码
?立即注册
搜索
热搜: 活动 交友 discuz
查看: 1250|回复: 1
打印 上一主题 下一主题

SQLite Joins

[复制链接]

803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
跳转到指定楼层
楼主
发表于 2015-6-1 16:32:05 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
转自:http://www.w3cschool.cc/sqlite/sqlite-joins.html
SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。

SQL 定义了三种主要类型的连接:

交叉连接 - CROSS JOIN

内连接 - INNER JOIN

外连接 - OUTER JOIN

在我们继续之前,让我们假设有两个表 COMPANY 和 DEPARTMENT。我们已经看到了用来填充 COMPANY 表的 INSERT 语句。现在让我们假设 COMPANY 表的记录列表如下:

ID? ?? ?? ? NAME? ?? ???AGE? ?? ?? ?ADDRESS? ???SALARY
----------??----------??----------??----------??----------
1? ?? ?? ???Paul? ?? ???32? ?? ?? ? California??20000.0
2? ?? ?? ???Allen? ?? ? 25? ?? ?? ? Texas? ?? ? 15000.0
3? ?? ?? ???Teddy? ?? ? 23? ?? ?? ? Norway? ?? ?20000.0
4? ?? ?? ???Mark? ?? ???25? ?? ?? ? Rich-Mond? ?65000.0
5? ?? ?? ???David? ?? ? 27? ?? ?? ? Texas? ?? ? 85000.0
6? ?? ?? ???Kim? ?? ?? ?22? ?? ?? ? South-Hall??45000.0
7? ?? ?? ???James? ?? ? 24? ?? ?? ? Houston? ???10000.0
另一个表是 DEPARTMENT,定义如下:

CREATE TABLE DEPARTMENT(
? ?ID INT PRIMARY KEY? ?? ?NOT NULL,
? ?DEPT? ?? ?? ???CHAR(50) NOT NULL,
? ?EMP_ID? ?? ?? ?INT? ?? ?NOT NULL
);
下面是填充 DEPARTMENT 表的 INSERT 语句:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );
最后,我们在 DEPARTMENT 表中有下列的记录列表:

ID? ?? ?? ? DEPT? ?? ???EMP_ID
----------??----------??----------
1? ?? ?? ???IT Billing??1
2? ?? ?? ???Engineerin??2
3? ?? ?? ???Finance? ???7
交叉连接 - CROSS JOIN
交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列,则结果表有 x+y 列。由于交叉连接(CROSS JOIN)有可能产生非常大的表,使用时必须谨慎,只在适当的时候使用它们。

下面是交叉连接(CROSS JOIN)的语法:

SELECT ... FROM table1 CROSS JOIN table2 ...
基于上面的表,我们可以写一个交叉连接(CROSS JOIN),如下所示:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
上面的查询会产生以下结果:

EMP_ID? ?? ?NAME? ?? ???DEPT
----------??----------??----------
1? ?? ?? ???Paul? ?? ???IT Billing
2? ?? ?? ???Paul? ?? ???Engineerin
7? ?? ?? ???Paul? ?? ???Finance
1? ?? ?? ???Allen? ?? ? IT Billing
2? ?? ?? ???Allen? ?? ? Engineerin
7? ?? ?? ???Allen? ?? ? Finance
1? ?? ?? ???Teddy? ?? ? IT Billing
2? ?? ?? ???Teddy? ?? ? Engineerin
7? ?? ?? ???Teddy? ?? ? Finance
1? ?? ?? ???Mark? ?? ???IT Billing
2? ?? ?? ???Mark? ?? ???Engineerin
7? ?? ?? ???Mark? ?? ???Finance
1? ?? ?? ???David? ?? ? IT Billing
2? ?? ?? ???David? ?? ? Engineerin
7? ?? ?? ???David? ?? ? Finance
1? ?? ?? ???Kim? ?? ?? ?IT Billing
2? ?? ?? ???Kim? ?? ?? ?Engineerin
7? ?? ?? ???Kim? ?? ?? ?Finance
1? ?? ?? ???James? ?? ? IT Billing
2? ?? ?? ???James? ?? ? Engineerin
7? ?? ?? ???James? ?? ? Finance
内连接 - INNER JOIN
内连接(INNER JOIN)根据连接谓词结合两个表(table1 和 table2)的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较,找到所有满足连接谓词的行的匹配对。当满足连接谓词时,A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接(INNER JOIN)是最常见的连接类型,是默认的连接类型。INNER 关键字是可选的。

下面是内连接(INNER JOIN)的语法:

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
为了避免冗余,并保持较短的措辞,可以使用 USING 表达式声明内连接(INNER JOIN)条件。这个表达式指定一个或多个列的列表:

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
自然连接(NATURAL JOIN)类似于 JOIN...USING,只是它会自动测试存在两个表中的每一列的值之间相等值:

SELECT ... FROM table1 NATURAL JOIN table2...
基于上面的表,我们可以写一个内连接(INNER JOIN),如下所示:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
? ?? ???ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查询会产生以下结果:

EMP_ID? ?? ?NAME? ?? ???DEPT
----------??----------??----------
1? ?? ?? ???Paul? ?? ???IT Billing
2? ?? ?? ???Allen? ?? ? Engineerin
7? ?? ?? ???James? ?? ? Finance
外连接 - OUTER JOIN
外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然 SQL 标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite 只支持 左外连接(LEFT OUTER JOIN)。

外连接(OUTER JOIN)声明条件的方法与内连接(INNER JOIN)是相同的,使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成,外连接(OUTER JOIN)将从一个或两个表中任何未连接的行合并进来,外连接的列使用 NULL 值,将它们附加到结果表中。

下面是左外连接(LEFT OUTER JOIN)的语法:

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
为了避免冗余,并保持较短的措辞,可以使用 USING 表达式声明外连接(OUTER JOIN)条件。这个表达式指定一个或多个列的列表:

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
基于上面的表,我们可以写一个外连接(OUTER JOIN),如下所示:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
? ?? ???ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查询会产生以下结果:

EMP_ID? ?? ?NAME? ?? ???DEPT
----------??----------??----------
1? ?? ?? ???Paul? ?? ???IT Billing
2? ?? ?? ???Allen? ?? ? Engineerin
? ?? ?? ?? ?Teddy
? ?? ?? ?? ?Mark
? ?? ?? ?? ?David
? ?? ?? ?? ?Kim
7? ?? ?? ???James? ?? ? Finance

803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
沙发
?楼主| 发表于 2015-6-6 05:48:26 | 只看该作者
转自:http://blog.csdn.net/coroutines/article/details/39369977
前一阵子设计数据库结构,有个设计是要保存所有的音、视频和图片信息的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。这样得到的表与我们之前设计那个全集结果一样,但数据结构更清晰,空间占用更少。

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表