SQL中的各种JOIN

有人说，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 --

相关文章

• SQLite的事务模式
• sqlite3外键约束引用的列要有unique约束
• 详解SQLite3的column type affinity
• SQLite3函数：last_insert_rowid()
• SQLite3的primary key可以为null，但有个例外
• 测试sqlite3 autocommit的相对速度
• UPDATE语句的应用
• 在python中多线程访问sqlite3数据库
• SQLite的System Catalogs
• SQLite3的ALTER TABLE语句
• SQLite3的锁机制
• 数据库设计之表间的三种关系
• 在sqlite3中启用外键约束
• SQLite3的DDL，DML，TCL，DCL
• SQL的三元逻辑（Three-Valued Logic）
• SQLite的特有功能
• sqlite3.OperationalError: database is locked
• REPLACE语句的应用