[原] SQLITE 上 INNER JOIN,CROSS JOIN,FULL JOIN 的写法.
华芸智森 2011-10-27 02:08:47
/*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