本文分享主要描述了几种书写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时常见的隐含错误，在平时的工作中应当尽量注意避免，希望本文对你有所帮助。

公众号『大数据技术与数仓』，回复『资料』领取大数据资料包