SQL中的各种JOIN

2020年10月28日 / 8次阅读 / Last Modified 2020年10月28日
SQLite

有人说,join是sql中最基础最重要的操作,本文总结SQL语言中的各种JOIN语句的语法和含义。

CROSS 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

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,都没有这样的危险!

OUTER JOIN

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条留言

电子邮件地址不会被公开。 必填项已用*标注

  • 麦新杰

    还有一种cross join的shortcut:

    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
    1   7
    2   8
    sqlite> select * from aa,bb;
    id  sn  content  id  seq
    --  --  -------  --  ---
    1   1   a        4   4
    1   1   a        5   5
    1   1   a        1   7
    1   1   a        2   8
    2   2   b        4   4
    2   2   b        5   5
    2   2   b        1   7
    2   2   b        2   8
    3   3   c        4   4
    3   3   c        5   5
    3   3   c        1   7
    3   3   c        2   8
    
     [回复]


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top