写SQL的要注意的一些语法

目录

• 一、left join on后面的条件与where后面的条件的区别
• 二、子查询与JOIN&LEFT JOIN比较
• 三、count(*)、count(1)、count(id)的区别
• 四、mysql、oracle、hive中的substr()函数的区别
• 五、length()和char_length()的区别
• 六、union与union all的区别

一、left join on后面的条件与where后面的条件的区别

# 主表：A、关联表：B、关联条件：A.od=B.a_id、筛选条件：B.id=1
A left join B on A.id=B.a_id and B.id=1

关联表的筛选条件:

• 如果放置在 on 后面，那么 A 和 B 的连接顺序为：B 表先按条件查询，再与 A 表连接，即先筛选再连接；

• 如果放置在 where 后面，那么 A 和 B 的连接顺序为：A 与 B 连接后，再从连接表中筛选，即先连接再筛选

所以，结论就是on 后面的筛选条件是针对于关联表，where 后面的条件是对连接后的数据进行筛选

二、子查询与JOIN&LEFT JOIN比较

我们在多数的开发过程中，会首先考虑实现过程，往往会使用多个子查询，但子查询虽然很灵活，但是执行效率并不高，原因：

• 执行子查询时，MySQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一些影响，这里多了一个创建和销毁临时表的过程。

三、count(*)、count(1)、count(id)的区别

• 1、count(*) 包含null值，统计所有行数；count(id) 不包含null值；

• 2、如果你的数据表没有主键，那么count(1)比count()快 ；如果有主键的话，那主键（联合主键）作为count的条件也比count()要快 。

• 3、如果你的表只有一个字段的话那count(*)就是最快的。

• 4、如果count(1)是聚索引,id,那肯定是count(1)快,但是差的很小的。因为count(),自动会优化指定到那一个字段。所以没必要去count(1)，用count(),sql会帮你完成优化。此时count(1)和count(*)基本没有区别!

四、mysql、oracle、hive中的substr()函数的区别

• 1、substr作为截取字符串的函数，用法是substr(字段名(string) , 起始位置(int) , 截取长度(int))

• 2、mysql的substr() 函数，mysql的起始位置只能从1开始，不能从0开始。

• 3、oracle的substr() 起始位置从0和从1开始都是表示从字符串首位开始截取

• 4、hive 则是从1开始

五、length()和char_length()的区别

• 1、char_length(str)
  （1）计算单位：字符
  （2）不管汉字还是数字或者是字母都算是一个字符

• 2、length(str)
  （1）计算单位：字节
  （2）utf8编码：一个汉字三个字节，一个数字或字母一个字节。
  （3）gbk编码：一个汉字两个字节，一个数字或字母一个字节。

六、union与union all的区别

• 1、对重复结果的处理：UNION会去掉重复记录，UNION ALL不会；

• 2、对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合并；

• 3、效率方面的区别：因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多；