Note: 以下均以SQLite为例。
1 SQL Join 操作
Join 操作包括以下四种：
- Inner Join（内连接）：在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
- Outer Join （外连接）：
- Left Join/Left Outer Join （左连接，左外连接）：Left join（左连接）会从左表中返回所有的记录，即使某条记录在右表中没有与之匹配的行。
- Right Join/Right Outer Join （右连接，右外连接）：Right outer join（右连接）会返回右表中的所有记录，即使某条记录在左表中没有与之匹配的行。
- FULL Join/FULL Outer Join （全连接，全外连接）：只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
- CROSS JOIN/ Cross Product/ Cartesian（笛卡尔积、交叉连接）：CROSS JOIN 返回的结果为被连接的两个表的乘积。
- Natural join（自然连接，R⋈S）：自然连接（Natural join）是一种特殊的等值连接，要求两个关系表中进行比较的属性组必须是名称相同的属性组，并且在结果中把重复的属性列去掉（即：对名称相同的属性组只显示一列）。
1.1 Inner Join（内连接）
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
例子
T1表
| id_t1 | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
T2表
| id_t2 | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 inner join T2 on id_t1 = id_t2;
注意，这里的inner关键字是可以省略的，即等效于
select * from T1 join T2 on id_t1 = id_t2;
结果
| id_t1 | c1_t1 | id_t2 | c1_t2 |
|---|---|---|---|
| 2 | bbb | 2 | ccc |
| 2 | bbb | 2 | ddd |
内连接（inner join）与等值连接的区别
- 内连接：两个表（或连接）中某一数据项相等的连接称，一般用
on子句设置条件 - 等值连接：一般用
where子句设置条件
经常有人会问到select a.id,b.name from a,b where a.id=b.pid 与select a.id,b.name from a inner join b on a.id=b.pid 有什么区别，哪个效率更高一些。
先给结论：内连接与等值连接其实是等效。
我们来做个试验：
在sqlite中，我们在执行SQL时，可以用explain query plan来显式地查看一条sql执行的具体过程。
事实上，两种具有不同表达方式的SQL表达式在被执行前，先被解析（parse）成抽象语法树（abstruct syntax tree），语法树被翻译（translate）成代数表达式（algebraic expression），进而代数表达式被优化器（optimizer）优化【通过改写（rewrite）成**权威代数表达式（canonical algebraic expression）**的方式】，最终权威代数表达被执行器（execution engine）执行。
从上面的截图中，可以看出SQL执行器在分别对这两条SQL做join时，都是先全表扫描T2表，再利用T1表中基本主键id1列上的索引做hash join。这说明这两条sql对应的权威代数表达式其实是完全一致的.
因此，我们可以说内连接与等值连接其实是等效，只是两种不同的表达形式罢了。
1.2 外连接（Outer Join）
外连接分为三种：左外连接（Left Outer Join）、右外连接（Right Outer Join）和全外连接（Full Outer Join）。
1.2.1 Left Join/Left Outer Join （左连接，左外连接）
Left join（左连接）会从左表(shop)中返回所有的记录，即使某条记录在右表(sale_detail)中没有与之匹配的行。
例子
T1表：
| id_t1 | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
| 5 | ggg |
T2表：
| id_t2 | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 left join T2 on id_t1 = id_t2;
结果
| id_t1 | c1_t1 | id_t2 | c1_t2 |
|---|---|---|---|
| 1 | aaa | ||
| 2 | bbb | 2 | ccc |
| 2 | bbb | 2 | ddd |
| 5 | ggg |
分析
注意到T2中还有一个id_t2=3，c1_t2=eee的纪录，但是没有在结果中出现，而T1表中的id_t1=5, c1_t1=ggg的在T2中没有相应的纪录，但是却出现在了查询结果集中。
因为现在是left join，所有的工作以left为准。结果id为1、2的行都是既在左表又在右表的纪录，5是只在左表，不在右表的记录。
1.2.2 Right Join/Right Outer Join （右连接，右外连接）
Right outer join （右连接）会返回右表中的所有记录，即使某条记录在左表中没有与之匹配的行。
例子
T1表：
| id_t1 | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
T2表：
| id_t2 | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 right join T2 on id_t1 = id_t2;
结果
| id_t2 | c1_t2 | id_t1 | c1_t1 |
|---|---|---|---|
| 2 | ccc | 2 | bbb |
| 2 | ddd | 2 | bbb |
| 3 | eee |
1.2.3 FULL Join/FULL Outer Join （全连接，全外连接）
只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
例子
T1表
| id_t1 | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
T2表
| id_t2 | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 full join T2 on id_t1 = id_t2;
结果
| id_t2 | c1_t2 | id_t1 | c1_t1 |
|---|---|---|---|
| 1 | Aaa | ||
| 2 | ccc | 2 | bbb |
| 2 | ddd | 2 | bbb |
| 3 | eee |
分析
FULL JOIN 关键字会从左表 (T1) 和右表 (T2) 那里返回所有的行。即使 T1 中的某行在表 T2 中没有匹配，或者 T2 中的某行在表 T1 中没有匹配，这些行同样会被包含在查询结果集中。
1.3 CROSS JOIN/ Cross Product/ Cartesian（笛卡尔积、交叉连接）
CROSS JOIN 返回的结果为被连接的两个表的乘积。
例子
T1表
| id_t1 | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
T2表
| id_t2 | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 cross join T2;
等价于
select * from T1 join T2;
等价于
select * from T1, T2;
结果
| id_t2 | c1_t2 | id_t1 | c1_t1 |
|---|---|---|---|
| 1 | aaa | 2 | ccc |
| 1 | aaa | 2 | ddd |
| 1 | aaa | 3 | eee |
| 2 | bbb | 2 | ccc |
| 2 | bbb | 2 | ddd |
| 2 | bbb | 3 | eee |
分析
可以发现，T1表中只有2行数据，T2表中有3行数据，而查询结果就产生了2*3=6行数据。
事实上，Cross Join的查询代价是非常大的，因此我们应该尽量避免它（如果能用Inner Join或Outer Join来代替时）。
1.4 Natural join（自然连接，R⋈S）
自然连接（Natural join）是一种特殊的等值连接，要求两个关系表中进行比较的属性组必须是名称相同的属性组，并且在结果中把重复的属性列去掉（即：对名称相同的属性组只显示一列）。
例子
T1表
| id | c1_t1 |
|---|---|
| 1 | aaa |
| 2 | bbb |
| 5 | ggg |
T2表
| id | c1_t2 |
|---|---|
| 2 | ccc |
| 2 | ddd |
| 3 | eee |
SQL
select * from T1 natural join T2;
结果
| id | c1_t2 | id_t1 |
|---|---|---|
| 2 | bbb | ccc |
| 2 | bbb | ddd |
分析
自然连接将在两个表中，具有相同名称的属性上具有相同值的行记录进行匹配（表T1和表T2中的id属性相等的行记录），并且去掉重复的属性列（在结果集中id列只显示一次）。
那些没被匹配的行不出现在结果中，因此自然连接的结果会有数据丢失，这些丢失的数据就是那些没有匹配的数据。
自然连接是一种特殊的等值连接，其要求多个表有相同的属性字段，然后条件为相同的属性字段值相等，最后再将表中重复的属性字段去掉，即为自然连接。
如A表中a,b,c字段，B表中有c,d字段，则select * from A natural join B 相当于 select A.a,A.b,A.c,B.d from A.c = B.c 。
2 SQL UNION 操作符
2.1 UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
例子
Employees_China表
| E_ID | E_Name |
|---|---|
| 01 | Zhang, Hua |
| 02 | Wang, Wei |
| 03 | Carter, Thomas |
| 04 | Yang, Ming |
Employees_USA表
| E_ID | E_Name |
|---|---|
| 01 | Adams, John |
| 02 | Bush, George |
| 03 | Carter, Thomas |
| 04 | Gates, Bill |
SQL
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
结果
| E_Name |
|---|
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Gates, Bill |
分析
UNION 无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员（名字都叫Carter, Thomas），他们当中只有一个人被列出来了。UNION 只会选取不同的值。
2.2 UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 会列出所有的值（包括重复出现的值）。
SQL Statement 1
UNION ALL
SQL Statement 2
例子
列出在中国和美国的所有的雇员：
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
结果
| E_Name |
|---|
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
分析
可以发现，Carter, Thomas会出现两次。
3 SQL 聚合操作（Aggregation）
SQL中提供的聚合函数可以用来统计、求和、求最值等等。
分类：
- COUNT：统计行数量
- SUM：获取单个列的合计值
- AVG：计算某个列的平均值
- MAX：计算列的最大值
- MIN：计算列的最小值
SQL GROUP BY 子句
SQL GROUP BY 语法
聚合函数有时也会结合GROUP BY 使用，根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
我们拥有下面这个 “Orders” 表：
| O_Id | OrderDate | OrderPrice | Customer |
|---|---|---|---|
| 1 | 2008/12/29 | 1000 | Bush |
| 2 | 2008/11/23 | 1600 | Carter |
| 3 | 2008/10/05 | 700 | Bush |
| 4 | 2008/09/28 | 300 | Bush |
| 5 | 2008/08/06 | 2000 | Adams |
| 6 | 2008/07/21 | 100 | Carter |
现在，我们希望查找每个客户的总金额（总订单）。
我们想要使用 GROUP BY 语句对客户进行组合。
我们使用下列 SQL 语句：
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
结果集类似这样：
| Customer | SUM(OrderPrice) |
|---|---|
| Bush | 2000 |
| Carter | 1700 |
| Adams | 2000 |
SQL HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SQL HAVING 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SQL HAVING 实例
我们拥有下面这个 “Orders” 表：
| O_Id | OrderDate | OrderPrice | Customer |
|---|---|---|---|
| 1 | 2008/12/29 | 1000 | Bush |
| 2 | 2008/11/23 | 1600 | Carter |
| 3 | 2008/10/05 | 700 | Bush |
| 4 | 2008/09/28 | 300 | Bush |
| 5 | 2008/08/06 | 2000 | Adams |
| 6 | 2008/07/21 | 100 | Carter |
现在，我们希望查找订单总金额少于 2000 的客户。
我们使用如下 SQL 语句：
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
结果集类似：
| Customer | SUM(OrderPrice) |
|---|---|
| Carter | 1700 |