SQL select语句复习

内连接

概念

内连接，也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。可以理解为取两个表的交集

#

语法

select fieldlist from table1 [inner] join table2 on table1.column = table2.column

举例

A表
| id | name |
| ——— | ——— |
| 1 | tom |
| 2 | jerry |
| 3 | max |

B表
| id | stock |
| ——— | ——— |
| 1 | 15 |
| 2 | 50 |

select * from A inner join B on A.id = B.id

结果
| A.id | name | B.id |stock |
| ——— | ——— | ——— | ——— |
| 1 | tom | 1 | 15
| 2 | jerry | 2 | 50 |

外连接

概念

外连接不仅包含符合连接条件的行，还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。SQL外连接共有三种类型：左外连接(关键字为LEFT OUTER JOIN)、右外连接(关键字为RIGHT OUTER JOIN)和全外连接(关键字为FULL OUTER JOIN)。外连接的用法和内连接一样，只是将INNER JOIN关键字替换为相应的外连接关键字即可。

解释

• 1.外连接中outer关键字是可以省略的
• 2.通俗地讲，语句A left (outer) join 连接的记录数与表A的记录数相同，A right
  join B的连接记录数与B表记录数相同。A left join B等价于B right join A。

语法

select fieldlist from table1 left/right [outer] join table2 on table1.column = table2.column

举例

A表
| id | name |
| ——— | ——— |
| 1 | tom |
| 3 | jerry |
| 4 | max |

B表
| id | stock |
| ——— | ——— |
| 1 | 15 |
| 2 | 50 |
| 4 | 70 |

左连接语句

select * from A left join B on A.id = B.id

结果
| A.id | name | B.id |stock |
| ——— | ——— | ——— | ——— |
| 1 | tom | 1 | 15
| 3 | jerry | 2 | NULL |
| 4| max | 4 | 70|
可以看到，返回的结果集为A表全部记录，B表中没有的记录则返回NULL

右连接语句

select * from B left join A on A.id = B.id

结果
| B.id | stock | A.id |name |
| ——— | ——— | ——— | ——— |
| 1 | 15 | 1 | tom
| 2 | 50 | NULL | NULL |
| 4| 70 | 4 | max |

全连接语句

select * form A a full outer join B b on a.id = b.id

结果
| A.id | name | B.id |stock |
| ——— | ——— | ——— | ——— |
| 1 | tom | 1 | 15
| 3 | jerry | 2 | NULL |
| NULL | NULL | 2 | 50 |
| 4| max | 4 | 70|

group by

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”根据某一字段划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

举例

region	population	area	name
欧洲	15	2	英国
欧洲	50	3	法国
亚洲	70	4	中国
亚洲	20	3	日本

查找每个地区(region)的总人口数和总面积

select region,SUM(population),SUM(area) from table GROUP BY region

采用group by将集合根据地区划分为几个不同小集合后，sum()函数再针对每一个小集合进行计算。注意：表中除region(地区)外的字段，只能通过SUM COUNT等聚合函数运算后返回一个值

Having

Having子句可以让我们筛选通过Group by成组后的数据：HAVING子句在聚合后对组记录进行筛选。举个例子就明白了

举例

还是上边那张表，查找总面积大于6的地区的面积、人口总和

select region,SUM(population),SUM(area) as TotalArea from table GROUP BY region Having TotalArea > 6

练习（来自LeetCode）

查找Person表中所有重复的电子邮箱
Person
| id | Email |
| ——— | ——— |
| 1 | a@b.com |
| 3 | c@b.com |
| 4 | a@b.com |
解法1，效率较低

select DISTINCT p1.Email from Person p1,Person p2 where p1.Id != p2.Id and p1.Email = p2.Email

解法2，使用group by having 效率高

select Email from Person Group By Email Having count(Email)>1

in 和 exists

首先，看一道leetCode上的习题
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。Customers表有两个字段Id和name，Orders表有两个字段Id和CustomerId

解法1

select Name as Customers from Customers where not exists (select CustomerId from Orders where Customers.Id = Orders.CustomerId)

解法2

select Name as Customers from Customers where Id not in (select CustomerId from Orders)

以上两种解法都达到了目的，可是什么时候用哪种，如何选择？我们分别看下这两种语句的原理

in

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。内表就是子表，即上述例子中括号内查询的表

笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员.

例如：假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

exists

指定一个子查询，检测行的存在。首先遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

使用exits查询时，首先查询的是主表，对应上述例子，也就是

select  Name as Customers from Customers

然后根据查询到的每一条记录，执行后边的where语句，如果where后的语句成立则返回true，该行结果保留。如果返回的是false，则该行删除。

比较

我们从exists开始分析，看这样一条语句

select a.* from A a where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 它的查询过程类似于以下过程

List resultSet=[];
Array A=(select * from A)
for(int i=0;i<A.length;i++) {    
    if(exists(A[i].id) {
     //执行select 1 from B b where b.id=a.id是否有记录返回       
     resultSet.add(A[i]);
    }
}
return resultSet;

结论

当B表（可以叫做内表或者子表）数据较大时适合用exists(),如:A表有1千条记录,B表有十万条记录,那么exists()会执行一千次去判断A表中的id是否与B表中的id相等. 如:A表有一千条记录,B表有一百万条记录,那么exists()还是执行一千次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果. 再如:A表有一千条条记录,B表有一百条记录,那么exists()还是执行一千次,还不如使用in()遍历1000乘以100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

not in和not exists同理

​