SQL 中的连接查询
关于SQL的应用,肯定离不开查询,而相对复杂的查询,总是离不开对表的连接,单个表操作的并不罕见,但是在应用环境大多数的查询都是针对2、3个表甚至更多的表7,至于连接,有内连接、外链接、交叉连接之分,每种连接方式都有各自的查询关键字去执行。此时犹记学时对这些概念含糊不分,不知所谓,总是认为课本的知识玄幻深奥,概念晦涩难懂,当然我也时常归咎于是本校师生随手“复印”的教材。
一、 内连接(通过关联信息匹配数据)
1.等值连接(=,有重复) 2.不等值连接(不等式、大小于) 3.自然连接(=,无重复,通过过滤条件)
SELECT * FROM a,b WHERE a.id = b.id --(ANSI连接语法,SQL92标准以前的写法) SELECT * FROM a INNER JOIN b ON a.id = b.id --( SQL92标准写法)
二、 外连接
1. 左连接(Left Join) 不管能否匹配到 on 的条件,左表数据均会完整显示
2. 右连接(Right Join) 不管能否匹配到on的条件,右表数据均会完整显示
3. 全连接(Full Join) 一定条件下(列名、列数一致),可用Union all 代替
当出现多个外链接(三路外连接)时,比如,需要连接两个Left Join
则,A表由5条数据,第一次Left 结束结果是5条,第二次 Left 结束之后结果仍然还是为5条
三、交叉连接(笛卡尔积)
实际应用中很少接触,查询结果初看乱七八糟,互相匹配,也是所有Join 都要执行的第一个步骤
SELECT * FROM a CROSS JOIN b
以上只是些许概念,课本上网上都能查到,实际上在做这些查询的时候,往往伴随着条件的过滤,想要写好SQL,第一步是要明白这些条件的执行顺序
SQL的执行顺序:from > join > on > where > group by > having > select > distinct > order by > limit
至于比较混淆的几点:
① Where 和 On 的区别 (Inner中无差别)
首先搞清楚Left 和 Right 两个查询的内部执行顺序 1.笛卡儿积 2. On 关联过滤 3. 添加外部行(Inner时无此操作) 4. Where过滤
然后我们根据实际数据来说明差别,如下两表:
根据表Student表中的ID 和StudentScore 表的UserCode 进行关联,分别写出如下语句
试想,三种sql语句的区别, 第一个没有加额外的过滤, 第二个是放在 On后面过滤,第三个是放在where 后过滤
select a.ID,b.ClassId,b.Score from Student a left join StudetntScore b on a.ID = b.UserCode --条件放在On 后 select a.ID,b.ClassId,b.Score from Student a left join StudetntScore b on a.ID = b.UserCode and b.ClassId is not null
--条件放在Where 后
select a.ID,b.ClassId,b.Score from Student a
left join StudetntScore b on a.ID = b.UserCode where b.ClassId is not null
对应的查询结果如下
第一笔查询仅根据ID 关联的查询结果不难想象
第二笔查询虽然 只保留 b.ClassId is not null 的数据,其余的数据却在第三步添加外部行的时候又给加进来了,根据Left 左表完整的原则,补全后的栏位找不到值,自然为null
第三笔查询是在第一笔查询的结果上,where 检索 b.ClassId is not null 的数据
说到这里,还有一个地方需要注意,就是在检索后,select 选择列的时候,一定要分清楚关联列的所属表,否则,失之毫厘,擦之千里,比如,针对上面 第二个查询语句,如果随手手抖,如下:
select a.ID,b.ClassId,b.Score from Student a left join StudetntScore b on a.ID = b.UserCode and b.ClassId is not null select b.UserCode,b.ClassId,b.Score from Student a left join StudetntScore b on a.ID = b.UserCode and b.ClassId is not null
查询结果,俨然变成
列值就会出现变化,如果恰巧再对数据进行去重,Distinct,第二个表结果- 数据就变成了5条,差别更大了,故,进行连接操作时,一定要仔细分析情况,避免入坑。
② Where 和 Having 的区别
这一点其实是很好区分的,Having的使用 一定伴随着 聚合函数 Group by 的出现,Where 是对分组之前进行过滤,having 是对分组之后进行过滤