注意！這幾種SQL隱藏的錯誤

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

在運算符中使用null值

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

• 情況1:與null值列相加

比如有如下表：

當我們對每行的列字段相加時，會出現(xiàn)null值

SELECT
????Jan?+?Feb?+?Mar?as?total_amount?--?結(jié)果為null
FROM?stores
WHERE?store?=?1

比如：

SELECT?100?+?null?+?190;?--?結(jié)果為null

其實，我們計算的時候希望null值變?yōu)?，但是SQL并沒有幫我們轉(zhuǎn)換，所以遇到這種存在null值的情況，要加以小心，我們可以通過判斷進行處理，比如：

SELECT?100?+?coalesce(null,0)?+?190;?--?結(jié)果為290

• 情況2：not in 操作

比如有如下表：

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

SELECT
?????*
FROM?scores
WHERE?tag?not?in?("優(yōu)")

結(jié)果輸出為：

其實對于id=3的數(shù)據(jù)，也應該出現(xiàn)在結(jié)果里，但是沒有出現(xiàn)，是因為null判斷之后返回null值，比如：

select?null?not?in?("tag1","tag2");?--?返回null值
select?coalesce(null,"")?not?in?("tag1","tag2");?--?返回true

所以避免上述情況發(fā)生，需要使用判斷進行處理，比如如果為NULL值，則取值空字符串。

在聚合數(shù)據(jù)時使用null值

一般使用聚合函數(shù)時，需要特別注意聚合的字段是否包含null值。當然，如果是求和，比如sum，應該不會有所影響，因為null值不參與求和。但是，在我們求平均值的時候，需要格外注意null值，看下面的例子：

當我們對amount求平均時：

SELECT
????avg(amount)?as?avg_amount
FROM?orders

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

SELECT
????avg(coalesce(amount,0))?as?avg_amount
FROM?orders
--?結(jié)果為(150+150+0)/3=100

求平均值時使用判斷條件

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

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

SELECT
avg(if(status?=?1,amount,0))?as?avg_complete_amount
FROM?orders

猜一下上面的結(jié)果會是什么呢？100還是150呢？答案是100，這個結(jié)果顯然不是我們想要的，因為status=1的數(shù)據(jù)只有兩行，正確結(jié)果應該是：(150+150)/2=150.

那么怎么才能得到上面的結(jié)果呢？因為null值不參與計算，所以我們可以通過判斷，當不滿足條件時，取null值即可，SQL如下：下面SQL的判斷是：if(status = 1,amount,null)，這樣就可以輸出正確結(jié)果150了。

SELECT
avg(if(status?=?1,amount,null))?as?avg_complete_amount
FROM?orders

濾條件中使用and和or

當我們使用一個復雜的條件語句時，很容易犯一個運算符優(yōu)先級的錯誤。其中最為常見的就是：當編寫SQL時，and先于or進行處理。

比如有下面的表：

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

SELECT
????*
FROM
????user_amount
WHERE?rating?=?"A"?OR?rating?=?"B"?AND?amount?>?100;

上面的結(jié)果會輸出：

1???????A???????50?
4???????A???????150
5???????B???????126

很顯然結(jié)果是錯誤的，這是什么原因的，細心的你會發(fā)現(xiàn)，由于AND條件的優(yōu)先級高于OR，所以上面的SQL語句會先篩選條件為rating = "B" AND amount > 100的數(shù)據(jù)，然后在篩選條件為**rating = "A"**的數(shù)據(jù)。解決上面的問題只需要加一個括號，區(qū)分一下優(yōu)先級即可。如下：

SELECT
*
FROM
user_amount
WHERE?(rating?=?"A"?OR?rating?=?"B")?AND?amount?>?100;
--?結(jié)果為：
4???????A???????150
5???????B???????126

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

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

SELECT
?????clo1,
?????clo2?clo3
FROM?tbl

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

inner join與left join

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

總結(jié)

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