MySql从一窍不通到入门（三）连接查询、联合查询、子查询

转载：MySQL数据高级查询之连接查询、联合查询、子查询

转载：MySQL里面的子查询

转载：分析比较多表查询中的IN与JOIN

转载：小表驱动大表, 兼论exists和in

转载：

一 连接查询

连接查询: 将多张表(>=2)进行记录的连接(按照某个指定的条件进行数据拼接)。

连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表。

连接查询: join, 使用方式: 左表 join 右表；左表: 在join关键字左边的表；右表: 在join关键字右边的表。

连接查询分类：SQL中将连接查询分成四类: 内连接,外连接,自然连接和交叉连接

交叉连接：交叉连接:cross join, 从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配: 匹配一定保留(没有条件匹配), 而连接本身字段就会增加(保留),最终形成的结果叫做:笛卡尔积。但是基本不会用到（反正我是从没有用过）。

基本语法: select * from 左表 cross join 右表 或select * from 左表,右表;

内连接: [inner] join, 从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

基本语法：select * from 左表 [inner] join 右表 on 左表.字段 = 右表.字段; 

1. on表示连接条件: 条件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)
2. 字段别名以及表别名的使用: 在查询数据的时候,不同表有同名字段,这个时候需要加上表名才能区分, 而表名太长, 通常可以使用别名.
3. 内连接可以没有连接条件: 没有on之后的内容,这个时候系统会保留所有结果(笛卡尔积)
4. 内连接还可以使用where代替on关键字，但效率差很多。

外连接: 以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接:不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL.

外连接分为两种: 是以某张表为主: 有主表

left join: 左外连接(左连接), 以左表为主表

right join: 右外连接(右连接), 以右表为主表

基本语法: select * from 左表 left/right join 右表 on 左表.字段 = 右表.字段;

二 联合查询

联合查询:将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)
基本语法:多条select语句构成: 每一条select语句获取的字段数必须严格一致(但是字段类型无关)
Select 语句1 Union [union选项] Select语句2...
Union选项: 与select选项一样有两个
All: 保留所有(不管重复)
Distinct: 去重(整个重复): 默认的

联合查询的意义:
1. 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.
2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

Order by使用
在联合查询中: order by不能直接使用,需要对查询语句使用括号才行；另外，要orderby生效: 必须搭配limit: limit使用限定的最大数即可.

select * from (select id,title from subjects where id>#some_id# order by id limit 1 ) as t1 

union 

select id,title from subjects where id<#some_id# order by id limit 1

三 子查询

子查询: 查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).

子查询分类

子查询有两种分类方式: 按位置分类;和按结果分类

• 按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

1. From子查询: 子查询跟在from之后

2. Where子查询: 子查询出现where条件中

3. Exists子查询: 子查询出现在exists里面

• 按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

1. 标量子查询: 子查询得到的结果是一行一列，可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧 

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

2. 列子查询: 子查询得到的结果是一列多行，可以使用 = />/ </ >=/ <=/ <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧 ，可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些操作符比较标量结果。

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

 
特殊情况 
　　如果 table2 为空表，则 ALL 后的结果为 TRUE； 
　　如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。 
注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

3. 行子查询: 子查询得到的结果是多列一行(多行多列) （1,2,3出现的位置都是在where之后)

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)注：(1,2) 等同于 row(1,2)SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4. 表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

子查询的相关性

sql语句1：获得所有hangzhou顾客的订单号。

select order_idfrom table2where customer_id in          (select customer_id          from table1          where city='hangzhou');

sql语句2：获得城市为hangzhou，并且存在订单的用户。

select *from table1where city='hangzhou' and exists                (select *                from table2                where table1.customer_id=table2.customer_id);

上面的两条sql语句，虽然例子举的有点不是很恰当，但是足以说明这里的问题了。
对于sql语句1，我们将子查询单独复制出来，也是可以单独执行的，就是子查询与外部查询没有任何关系。
对于sql语句2，我们将子查询单独复制出来，就无法单独执行了，由于sql语句2的子查询依赖外部查询的某些字段，这就导致子查询就依赖外部查询，就产生了相关性。

对于子查询，很多时候都会考虑到效率的问题。当我们执行一个select语句时，可以加上explain关键字，用来查看查询类型，查询时使用的索引以及其它等等信息。比如这么用：

explain select order_id  from table2  where customer_id in            (select customer_id            from table1            where city='hangzhou');

使用独立子查询，如果子查询部分对集合的最大遍历次数为n，外部查询的最大遍历次数为m时，我们可以记为：O(m+n)。而如果使用相关子查询，它的遍历 次数可能会达到O(m+m*n)。可以看到，效率就会成倍的下降；所以，在使用子查询时，一定要考虑到子查询的相关性。

in和exist

IN子查询
使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。
in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。
有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思。

ANY子查询

any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。
好比“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意一个进行比较，得到TRUE时，就会返回TRU

ALL子查询
all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。
好比“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。
<>all的同义词是not in，表示不等于集合中的所有值，这个很容易和<>any搞混，平时多留点心就好了。

EXISTS谓词
EXISTS是一个非常牛叉的谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其它谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN，对于EXISTS来说，UNKNOWN就是FALSE。 还是上面的语句，获得城市为hangzhou，并且存在订单的用户。

select *from table1where city='hangzhou' and exists                (select *                from table2                where table1.customer_id=table2.customer_id);

关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外， 还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样，SQL优化器会选择相同的执行计划。

说到了IN和EXISTS几乎是一样的，但是，就不得不说到NOT IN和NOT EXISTS，对于输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN就会得到NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。

四 IN和JOIN的效率对比

1. 非相关（无索引）的多表查询中，使用IN与JOIN的查询都是先将外部表的查询结果加入到连接缓冲区，再从内部表拿取数据进入缓冲区进行比较（嵌套循环）。查询计划几乎没有区别。但是，IN存在优先级的关系，比JOIN多了一次subquery的查询，在这种情况下，JOIN更优。

2. 当两张表相关（外键相连）时，无论是IN还是JOIN，联合查找都是一个参照的过程。

五 IN和EXIT的效率对比

给出两个表，A和B，A和B表的数据量，

1. 当A小于B时，用exists

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

exists的实现，相当于外表循环，每次循环对内表进行查询:

for i in A    for j in B        if j.id == i.id then ....

2. 如果A大于B的时候，则用in

select * from A where id in (select id from B)

这种在逻辑上类似于

for i in B    for j in A        if j.id == i.id then ....