2020年10月28日 / 17次阅读 / Last Modified 2020年10月28日
SQLite
有人说,join是sql中最基础最重要的操作,本文总结SQL语言中的各种JOIN语句的语法和含义。
又称为笛卡尔乘积(Carterian Product),cross join简单粗暴,两个表格分别有a列和b列,分别有m行和n行,cross join之后得到的表格,就是a+b列,m*n行。
sqlite> create table aa(id integer primary key, sn int, content text);
sqlite> insert into aa(id,sn,content) values (null,1,'a');
sqlite> insert into aa(id,sn,content) values (null,2,'b');
sqlite> insert into aa(id,sn,content) values (null,3,'c');
sqlite> select * from aa;
1|1|a
2|2|b
3|3|c
sqlite> .mode column
sqlite> .header on
sqlite> create table bb(id int unique not null, seq int);
sqlite> insert into bb(id,seq) values (4,4);
sqlite> insert into bb(id,seq) values (5,5);
sqlite> select * from bb;
id seq
-- ---
4 4
5 5
sqlite> select * from aa cross join bb;
id sn content id seq
-- -- ------- -- ---
1 1 a 4 4
1 1 a 5 5
2 2 b 4 4
2 2 b 5 5
3 3 c 4 4
3 3 c 5 5
sqlite>
sqlite> select * from aa join bb;
id sn content id seq
-- -- ------- -- ---
1 1 a 4 4
1 1 a 5 5
2 2 b 4 4
2 2 b 5 5
3 3 c 4 4
3 3 c 5 5
使用cross join需要注意,这个操作有可能会产生一个非常非常大的表格!你要知道你在做什么。。
INNER JOIN不带条件的时候,就是CROSS JOIN,如上示例。
两个表格分别a列和b列,m行和n行,inner join后的到的table,列数小于等于a+b,行数小于等于m*n。因为 inner join 有条件,因此得到的表格会比无条件的cross join小。
第1中表示条件的方式为 ON:
sqlite> select * from aa;
id sn content
-- -- -------
1 1 a
2 2 b
3 3 c
sqlite> select * from bb;
id seq
-- ---
4 4
5 5
sqlite> insert into bb(id,seq) values (1,7);
sqlite> insert into bb(id,seq) values (2,8);
sqlite> select * from aa join bb on aa.id=bb.id;
id sn content id seq
-- -- ------- -- ---
1 1 a 1 7
2 2 b 2 8
当aa.id=bb.id的row,才进入结果table。
使用 ON 来表示条件,是最通用的,但是表达式写起来稍微有点长,而且结果table会存在相同的列。
第2中表达条件的方式为 USING:
如果两个表的列名称相同,可以用USING来表达条件:
sqlite> select * from aa join bb on aa.id=bb.id;
id sn content id seq
-- -- ------- -- ---
1 1 a 1 7
2 2 b 2 8
sqlite> select * from aa join bb using(id);
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
sql表达式简单了写,而且结果table中的重复列也没有了。使用 USING 的前提是,列的名称要相同。
第3中表达条件的方式 NATURAL JOIN:
sqlite> select * from aa join bb using(id);
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
sqlite> select * from aa natural join bb;
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
NATURAL JOIN 会自动的去查看相同的列名,然后对他们进行 inner join 操作。
NATURAL JOIN 这个操作有一个危险点:因为它是自动去查看相同列名,如果两个表中没有相同的列名,它也会自动将 join 降级为 cross join。
sqlite> select * from sqlite_master;
type name tbl_name rootpage sql
----- --------------------- -------- -------- -------------------------------------------------------------
table aa aa 2 CREATE TABLE aa(id integer primary key, sn int, content text)
table bb bb 3 CREATE TABLE bb(id int unique not null, seq int)
index sqlite_autoindex_bb_1 bb 4
sqlite>
sqlite> create table cc(cat int, dog int);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> select * from aa natural join cc;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
sqlite> select * from aa join cc using(id);
Error: cannot join using column id - column not present in both tables
sqlite> select * from aa join cc on aa.id=cc.cat;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
而使用 ON 或者 USING,都没有这样的危险!
SQL语言定义了3中OUTER JOIN,分别是 LEFT, RIGHT和FULL,SQLite只支持 LEFT OUTER JOIN。
OUTER JOIN 是 INNER JOIN 的扩展。在执行完 INNER JOIN 的动作后,再讲LEFT table(左边的那个)中没有出现在结果table中的row,填入结果table,多出来的列(右边table中的列)填NULL。这就是OUTER JOIN。
sqlite> select * from aa outer join cc on aa.id=cc.cat;
Error: RIGHT and FULL OUTER JOINs are not currently supported
sqlite>
sqlite> select * from aa left outer join cc on aa.id=cc.cat;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
2 2 b
3 3 c
其实,在SQL语言表达式中,将右两个table互换,就是RIGHT OUTER JOIN了。
从 OUTER JOIN 的特点分析,LEFT table 中的所有row,都会一定出现的结果table中!
-- EOF --
本文链接:https://www.pynote.net/archives/2678
《SQL中的各种JOIN》有1条留言
Ctrl+D 收藏本页
©Copyright 麦新杰 Since 2019 Python笔记
还有一种cross join的shortcut:
[ ]