本文分享主要描述了几种书写SQL时常见的一些隐藏错误，主要包括：在运算符中使用null值、在聚合数据时使用null值、求平均值时使用判断条件、滤条件中使用and和or、查询的列字段之间缺少逗号分隔、inner join与left join。都是一些比较细节的点，希望本文对你有所帮助。
在运算符中使用null值
在SQL中常见的运算符有算术运算符(+,-)，逻辑运算符(in,not in)，比较运算符(>,<)等等。但是，如果参与运算的数据中存在null值，这个时候我们就要小心了，因为对于null值参与的运算可能会返回null值。
- 情况1:与null值列相加
比如有如下表：
store
Jan
Feb
Mar
1
100
null
190
2
200
160
150
3
150
200
180
当我们对每行的列字段相加时，会出现null值
SELECT
Jan + Feb + Mar as total_amount -- 结果为null
FROM stores
WHERE store = 1
比如：
SELECT 100 + null + 190; -- 结果为null
其实，我们计算的时候希望null值变为0，但是SQL并没有帮我们转换，所以遇到这种存在null值的情况，要加以小心，我们可以通过判断进行处理，比如：
SELECT 100 + coalesce(null,0) + 190; -- 结果为290
- 情况2：not in 操作
比如有如下表：
id
tag
score
1
优
90
2
良
70
3
null
50
4
差
40
当我们使用下面语句进行查询时：
SELECT
*
FROM scores
WHERE tag not in ("优")
结果输出为：
id
tag
score
2
良
70
4
差
40
其实对于id=3的数据，也应该出现在结果里，但是没有出现，是因为null判断之后返回null值，比如：
select null not in ("tag1","tag2"); -- 返回null值
select coalesce(null,"") not in ("tag1","tag2"); -- 返回true所以避免上述情况发生，需要使用判断进行处理，比如如果为NULL值，则取值空字符串。
在聚合数据时使用null值
一般使用聚合函数时，需要特别注意聚合的字段是否包含null值。当然，如果是求和，比如sum，应该不会有所影响，因为null值不参与求和。但是，在我们求平均值的时候，需要格外注意null值，看下面的例子：
id
amount
1
150
2
150
3
null
当我们对amount求平均时：
SELECT
avg(amount) as avg_amount
FROM orders
上面的结果会是什么呢？是150还是100呢？答案是：(150+150)/2=150,这个数据显示不是正确的，因为忽略了一行数据，解决上述问题我们可以使用判断当为null值时，取值0,，比如：
SELECT
avg(coalesce(amount,0)) as avg_amount
FROM orders
-- 结果为(150+150+0)/3=100
求平均值时使用判断条件
还有一种情况是，当我们使用条件判断进行求平均值时，也要加以小心，稍不留神就会得到错误的结果，看下面的例子：如下表
id
amount
status
1
150
1
2
150
1
3
300
0
当我们使用条件判断，求平均值时：
SELECT
avg(if(status = 1,amount,0)) as avg_complete_amount
FROM orders
猜一下上面的结果会是什么呢？100还是150呢？答案是100，这个结果显然不是我们想要的，因为status=1的数据只有两行，正确结果应该是：(150+150)/2=150.
那么怎么才能得到上面的结果呢？因为null值不参与计算，所以我们可以通过判断，当不满足条件时，取null值即可，SQL如下：下面SQL的判断是：if(status = 1,amount,null)，这样就可以输出正确结果150了。
SELECT
avg(if(status = 1,amount,null)) as avg_complete_amount
FROM orders
滤条件中使用and和or
当我们使用一个复杂的条件语句时，很容易犯一个运算符优先级的错误。其中最为常见的就是：当编写SQL时，and先于or进行处理。
比如有下面的表：
uid
rating
amount
1
A
50
2
B
75
3
C
50
4
A
150
5
B
126
当我们要查询，用户等级rating为A或者B时，并且amount大于100的数据时：
SELECT
*
FROM
user_amount
WHERE rating = "A" OR rating = "B" AND amount > 100;
上面的结果会输出：
1 A 50
4 A 150
5 B 126
很显然结果是错误的，这是什么原因的，细心的你会发现，由于AND条件的优先级高于OR，所以上面的SQL语句会先筛选条件为rating = "B" AND amount > 100的数据，然后在筛选条件为**rating = "A"**的数据。解决上面的问题只需要加一个括号，区分一下优先级即可。如下：
SELECT
*
FROM
user_amount
WHERE (rating = "A" OR rating = "B") AND amount > 100;
-- 结果为：
4 A 150
5 B 126
查询的列字段之间缺少逗号分隔
如果不细心，会造成这种情况出现，就是一个字段作为了前面字段的别名，比如：
SELECT
clo1,
clo2 clo3
FROM tbl
上面的sql语句是可以被执行的，但是结果是col2的值对应的别名为clo3，并且数据会少一列，会让人感觉很诧异。
inner join与left join
这种情况一般在没有搞清楚表之间的关系时，或者主表选择不清楚时会出现，一旦使用了错误的JOIN类型，得到的结果可能会多也可能会少。所以在进行连接查询时，区分JOIN类型也是值得注意的，下图是JOIN类型之间的区别。
总结
本文主要分享了一些书写SQL时常见的隐含错误，在平时的工作中应当尽量注意避免，希望本文对你有所帮助。
END