本文分享主要描述了幾種書寫SQL時常見的一些隱藏錯誤，主要包括：在運算符中使用null值、在聚合數據時使用null值、求平均值時使用判斷條件、濾條件中使用and和or、查詢的列字段之間缺少逗號分隔、inner join與left join。都是一些比較細節的點，希望本文對你有所幫助。

在運算符中使用null值

在SQL中常見的運算符有算術運算符(+,-)，邏輯運算符(in,not in)，比較運算符(>,<)等等。但是，如果參與運算的數據中存在null值，這個時候我們就要小心了，因為對于null值參與的運算可能會返回null值。

• 情況1:與null值列相加

比如有如下表：

當我們對每行的列字段相加時，會出現null值

SELECTJan + 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 操作

比如有如下表：

當我們使用下面語句進行查詢時：

SELECT* FROM scores WHERE tag not in ("優")

結果輸出為：

其實對于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值，看下面的例子：

當我們對amount求平均時：

SELECTavg(amount) as avg_amount FROM orders

上面的結果會是什么呢？是150還是100呢？答案是：(150+150)/2=150,這個數據顯示不是正確的，因為忽略了一行數據，解決上述問題我們可以使用判斷當為null值時，取值0,，比如：

SELECTavg(coalesce(amount,0)) as avg_amount FROM orders -- 結果為(150+150+0)/3=100

求平均值時使用判斷條件

還有一種情況是，當我們使用條件判斷進行求平均值時，也要加以小心，稍不留神就會得到錯誤的結果，看下面的例子：如下表

當我們使用條件判斷，求平均值時：

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進行處理。

比如有下面的表：

當我們要查詢，用戶等級rating為A或者B時，并且amount大于100的數據時：

SELECT* FROMuser_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

查詢的列字段之間缺少逗號分隔

如果不細心，會造成這種情況出現，就是一個字段作為了前面字段的別名，比如：

SELECTclo1,clo2 clo3 FROM tbl

上面的sql語句是可以被執行的，但是結果是col2的值對應的別名為clo3，并且數據會少一列，會讓人感覺很詫異。

inner join與left join

這種情況一般在沒有搞清楚表之間的關系時，或者主表選擇不清楚時會出現，一旦使用了錯誤的JOIN類型，得到的結果可能會多也可能會少。所以在進行連接查詢時，區分JOIN類型也是值得注意的，下圖是JOIN類型之間的區別。

總結

本文主要分享了一些書寫SQL時常見的隱含錯誤，在平時的工作中應當盡量注意避免，希望本文對你有所幫助。

Hive SQL使用過程中的奇怪現象?mp.weixin.qq.com數倉面試|四個在工作后才知道的SQL密技?mp.weixin.qq.com數倉規范|使SQL更易于閱讀的幾個小技巧?mp.weixin.qq.comSQL查詢的底層運行原理分析?mp.weixin.qq.com

總結

以上是生活随笔為你收集整理的区分错误类型_数仓|几种SQL隐藏的错误，你遇到过吗？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。