连接查询、子查询、联合查询

连接查询、子查询、联合查询

连接查询

交叉连接 t1 cross join t2

内连接：t1 inner join t2 on 。。满足条件的交集

左外连接： t1 left join t2 on 。。 以左表为主表，取出每一条与另一张对比，条件不匹配非主表字段都置位null

自然内连接和外连接： t1 natural t2， t1 natural left t2以同名字段连接

全连接Full outer join：会产生两个表的并集，不匹配的地方会被置位null，结果集中会出现两个name相同的，以及某一个name为null的结果（所有的连接都是这样，除了内连接，内连接的on和where一样）

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

选择两个表的去除交集的数据集：（控制主键为null，这样相当于除掉交集）

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null

Mysql没有全连接，只能用这种语法实现full join：（两次外连接union）

select * from t1 left join t2 on t1.id = t2.id

union

​ select * from t1 right join t2 on t1.id = t2.id;

mysql只能用这种方式实现两个表去除交集的数据集：（两次外连接同时用where限制null然后union）

select * from t1 left join t2 on t1.id = t2.id where t2.id is null

​ -> union

-> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

左表独有数据（利用置空另一张表的主键）：select * from t1 left join t2 on t1.id = t2.id where t2.id is null;

连接分为首先要查的表（驱动表）和被驱动表，对外连接来说这个是固定的，内连接根据优化器优化结果来确定谁作为什么表。驱动表查询的结果放入一个叫join buffer的区域，然后查询被驱动表依次与join buffer中的数据对比。

内连接中where和on是等效的，from后跟多个数据源就相当于内连接，外连接中不符合on条件的记录最后也会在结果中，不符合where的不会加入结果

子查询

分为标量子查询（只有一个数作为结果的查询）、行子查询（返回一个行记录）、列子查询（返回一列值）、表子查询。

也可分为相关子查询（子查询内限制条件与外层表有关）、和不相关子查询

子查询在执行时首先会尝试是否能转换为两次独立的单表查询，然后再尝试转换为半连接，将子查询转换成一种类似连接查询的查询来执行，不同之处在于关心满足条件不再关心满足条件的记录多少，如果数据太多会将子查询结果保存在一个临时表内，这个表称为物化表（一般放在内存中除非实在太大），并为该表建立哈希索引或b+索引，还可能将in子查询转换成exists子查询以命中索引。

（半连接：假如有一条语句select * from s1 where key1 in(select key2 from s2)这条语句可以理解成如果对s1表某条记录，如果能在s2中找到一条或多条记录s2的key2=key1的，那么这条记录最后就会被加入结果集中，其实和连接查询很像：select s1.* from s1 inner join s2 on s1.key1 = s2.key2,只不过现在不关心s2中具体什么记录匹配，而是是否有记录匹配，这样的逻辑依然可以简化查询，被称为半连接）

联合查询

Select句1 union（all/distinct）select句2，查询结果拼接

拼接的两个select语句查询的列数和列数据类型必须相同，union会去处相同的记录，而union all会将相同的记录保留（所谓相同就是每个列都必须相同）