从笛卡尔积开始认识多表查询

一、笛卡尔积

在数学中，集合A和集合B的所有组合情况成为笛卡尔积。

在关系型数据库中，笛卡尔积（Cartesian product）是指将两个表的所有行进行组合，生成一个新的结果集，其中每一行都包含了两个表的所有可能组合。

假设有两个表 A 和 B，每个表包含多行数据。当没有指定连接条件或连接条件不正确时，执行两个表的笛卡尔积操作将生成一个结果集，其中包含了表 A 的每一行与表 B 的每一行的组合。

Staff表:

Section表:

执行语句⇒ select * from staff,section

上述的SQL语句因为没有明确连接条件，导致出现了笛卡尔积；

1. staff表有6条数据，section表有7条数据;
2. staff表中的每一条数据都会与section表的每一条数据进行连接匹对，例如staff_id = 1的记录会与section_id为1到7的数据进行匹对，产生6x7条=42条数据。

二、多表查询

1. Inner join 内连接

为了消除笛卡尔积，我们通常需要为多表查询添加关联条件；
如下的查询语句: select * from staff,section where staff.section_id = section.id;

1. 上述查询语句实际上就是Inner join内连接:
2. 在 mysql 中，select * from a inner join b on a.id = b.id 在底层执行上，因为Inner join并不能确定左右表的数据，在没有索引时，都需要对表进行全表扫描；
3. 上述(2)中的查询语句底层实际执行为:
   select * from a join b where a.id = b.id

上述的Sql语句都属于是内连接，其中第一种称为显式内连接,第二种成为隐式内连接。

自连接:一种特殊的内连接

1. 表与其自身进行连接，根据条件进行记录匹配。
2. 为了区别表的，每一次出现，需要为表分布定义别名。
3. 为避免混淆，查询字段钱应添加表名作为前缀。
4. 自链接是内连接的特例。
5. 若要在一个表中查找具有相同列值的行，可以使用自连接

2. left/right [outer]join 左/右外连接

左连接和右连接一样，其中的区别只在于基表所处的位置，left join的基表在左，right join的基表在右，因此不再重复赘述右连接(right join) 。

Sql语句：

1. LEFT OUTER JOIN 中的OUTER可以省略，通常为left join。
2. 以左/右为基表，与另一张表逐行匹配；左/右表(基表)需要进行全表扫描，返回的结果一定会包含基表的所有记录。
3. 通过关联条件对副表进行逐行匹配，只有匹配ON条件的记录才会返回，如果左/右表(基表)中的记录在副表中没有找到与之匹配的记录，则使用null进行填充。

左外连接相当于查询左表的所有记录，当然也包含左表和右表交集部分的数据。

left join: A+B+C部分(黄色数据+红色数据)
right join: C+D+E部分(绿色数据+红色数据)
join(inner join) : C部分(红色数据)

goods表:

orders表:

执行Sql: select * from goods left join orders on goods.id = orders.id;

3. 子查询

SELECT * FROM {*|字段} FROM t1 WHERE Condition

当上述Condition不能够直接获取时，通常需要通过另一个查询语句获取查询条件;

子查询是将一个SELECT语句的查询结果作为中间结果，供另一个Sql语句调用

子查询可以应用在INSERT、UPDATE 、DELETE、 SELECT 语句中。

现用这两张表做案例:

1. 单值子查询

子查询中最简单的一种方式，其返回结果为单个值(数字，日期，字符串等)

select * from t1 where column1 [=|>|<] (select column2 from t2 where Condition)

2. EXISTS子查询

EXISTS用于测试子查询的结果是否为空表，若子查询结果不为空，EXISTS返回True，外层Sql语句可以执行；否则返回False，外层Sql语句不执行。

select * from t1 where EXISTS (select column2 from t2 where Condition)

 查询orders表中是否存在goods_id值为5的记录，如果存在，则查询goods表中id值小于5的记录。

查询语句:

select * 
from goods 
where id < 5 and exists (select o_id from orders where goods_id==5);

执行结果:

3. 行子查询

当子查询结果返回的记录是一行时。

语法格式:

行子查询得到的记录为一行多字段，其中子查询获取的字段需要和where中的字段逐一比较。

常用的条件判断符: =、<>(!=)、IN、NOT IN

查询goods表中与"果汁"的价格和数量相同的商品信息:	

查询语句:

select * 
from goods
where (price,num) = (select price,num) from goods where name = "果汁");

执行结果:

4. 表子查询

当子查询的结果返回的是多行多列(表)的时候；

语法格式:

表子查询返回的结果是多行多列的，可以理解为一张表；

查询goods表中与"果汁" 或 "西瓜" 的价格和数量相等的商品信息

查询语句:

select * 
from goods
where (price,num) in (select price,num from goods where name="果汁" or name="西瓜")

执行结果:

5. 列子查询

子查询返回的结果是一列，这种子查询成为列子查询；

常用操作符: IN、NOT IN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围内，一个字段的值是否存在于集合中
NOT IN	在指定的集合范围内，一个字段的值不存在于集合中
ANY	子查询返回列表中，有任意一个满足即可，表示满足其中任一条件即可
SOME	等于ANY等同，使用SOME的地方都可以用ANY
ALL	子查询返回列表的所有值都必须满足

在goods表中查询2018年4月2日以后的订单中的商品信息

查询语句:

select *
from goods
where id = any(select goods_id from orders where add_time > '2018-04-02')

执行结果:

1. 子查询从orders表中查询出时间>2018-04-02的记录
2. 紧接着通过ANY从goods表中获取符合子查询条件的记录
3. 本质上就是查询出goods表中购买时间大于2018-04-02的记录