[原] SQLITE 上 INNER JOIN,CROSS JOIN,FULL JOIN 的写法.

[华芸智森]

/*SQLITE 的各种连接的写法*/
/*SQLITE只提供一个 LEFT JOIN ,但有时有些特殊的情况,我们的确要用到 INNER JOIN,CROSS JOIN,FULL JOIN 等语句,怎么办?*/
/*其实,只要变通一下,SQLITE也可以写出这些连接的.看下面的例子:*/
/* */
/*不能按 F5 一起运行,要一段一段括起来运行,下面的 GO 只是个人习惯.*/

GO

/*定义两个测试表*/
CREATE TABLE test1(
intid int not null,
varname varchar(32) not null,
PRIMARY KEY (intid));

GO

CREATE TABLE test2(
intid int not null,
varname varchar(32) not null,
varremark varchar(64) null,
PRIMARY KEY (intid));

GO

/*在测试表中插入数据*/
insert into test1 values(1,'aaaa1');
insert into test1 values(2,'bbbb1');
insert into test1 values(3,'cccc1');

insert into test2 values(1,'aaaa2','2a');
insert into test2 values(2,'bbbb2','2b');
insert into test2 values(3,'cccc2','2c');
insert into test2 values(4,'dddd2','2d');
insert into test2 values(5,'eeee2','2e');
insert into test2 values(6,'ffff3','2f');

go


/*左连接和右连接可以互换*/
select t1.*,t2.* from test1 as t1 left join test2 as t2 on t2.[intid]=t1.intid;

/*执行结果*/
RecNo intid varname intid_1 varname_1 varremark
----- ----- ------- ------- --------- ---------
1 1 aaaa1 1 aaaa2 2a
2 2 bbbb1 2 bbbb2 2b
3 3 cccc1 3 cccc2 2c

go

/*相当于一个 INNER JOIN */
select t1.*,t2.* from test1 as t1 left join test2 as t2 on t2.[intid]=t1.intid where not t2.[intid] is null;

/*执行结果*/
RecNo intid varname intid_1 varname_1 varremark
----- ----- ------- ------- --------- ---------
1 1 aaaa1 1 aaaa2 2a
2 2 bbbb1 2 bbbb2 2b
3 3 cccc1 3 cccc2 2c

go

/*相当于交叉连接 cross join*/
SELECT T1.*,T2.* FROM TEST1 AS T1,TEST2 AS T2;

/*执行结果*/
RecNo intid varname intid_1 varname_1 varremark
----- ----- ------- ------- --------- ---------
1 1 aaaa1 1 aaaa2 2a
2 1 aaaa1 2 bbbb2 2b
3 1 aaaa1 3 cccc2 2c
4 1 aaaa1 4 dddd2 2d
5 1 aaaa1 5 eeee2 2e
6 1 aaaa1 6 ffff3 2f
7 2 bbbb1 1 aaaa2 2a
8 2 bbbb1 2 bbbb2 2b
9 2 bbbb1 3 cccc2 2c
10 2 bbbb1 4 dddd2 2d
11 2 bbbb1 5 eeee2 2e
12 2 bbbb1 6 ffff3 2f
13 3 cccc1 1 aaaa2 2a
14 3 cccc1 2 bbbb2 2b
15 3 cccc1 3 cccc2 2c
16 3 cccc1 4 dddd2 2d
17 3 cccc1 5 eeee2 2e
18 3 cccc1 6 ffff3 2f

go

/*相当于全连接 .full join */
select t1.*,t2.* from test1 as t1 left OUTER JOIN test2 as t2 on t2.[intid]=t1.intid
UNION ALL
select t1.*,t2.* from test2 as t2 left join test1 as t1 on t2.[intid]=t1.intid and t2.intid is null;

/*执行结果*/
RecNo intid varname intid_1 varname_1 varremark
----- ------ ------- ------- --------- ---------
1 1 aaaa1 1 aaaa2 2a
2 2 bbbb1 2 bbbb2 2b
3 3 cccc1 3 cccc2 2c
4 (null) (null) 1 aaaa2 2a
5 (null) (null) 2 bbbb2 2b
6 (null) (null) 3 cccc2 2c
7 (null) (null) 4 dddd2 2d
8 (null) (null) 5 eeee2 2e
9 (null) (null) 6 ffff3 2f

[ACMAIN_CHM]

[wwwwb]

学习，SQLITE还有很多需要完善的地方

[memoryshy]

我之前做过的项目中，full join 和cross join用的比较少喔，其他的连接查询,像left join，right join，inner join用的倒是挺多的~~~理解了，就能在适当的场合运用最适用的sql了，谢谢楼主分享

[gaogaogzw]

学习~~~

[床上等您]

[咿呀大河马]

[dd4979590]

学习了，谢谢楼主

[yangyongaiwmm]

[Quote=引用楼主 mstop 的回复:]
/*SQLITE 的各种连接的写法*/
/*SQLITE只提供一个 LEFT JOIN ,但有时有些特殊的情况,我们的确要用到 INNER JOIN,CROSS JOIN,FULL JOIN 等语句,怎么办?*/
/*其实,只要变通一下,SQLITE也可以写出这些连接的.看下面的例子:*/
/* */
/*不能按 F5 一起运行,要一段一段括起来运行,下面的 GO 只是个人习……
[/Quote]

hj

[法尔我给他]

sqlite加密是我最关心的,不知道楼主有没有加密函数的实现?

[northcan]

正在WinCE嵌入式系统上的应用项目中使用Sqlite
非常小巧精美的数据库
感谢楼主分享

[萧炎]

[zhtgzhtg]

学习，SQLITE还有很多需要完善的地方 谢谢

[Sududdao]

谢谢，太有用了

[YHL27]

值得学习啊，sql

[gggkcb]

学习了，谢谢楼主

[izard999]

学习，SQLITE还有很多需要完善的地方

[xjlcao]

学习一下，谢谢