尋找並處理遺漏值

SQL Server 中級

Ginger Grant

Instructor

偵測遺漏值

當沒有資料時，資料庫欄位會是 NULL
NULL 不是數字，不能用 =、<、> 來尋找或比較遺漏值
若要判斷欄位是否為 NULL，請用 IS NULL 與 IS NOT NULL

T-SQL：回傳無 NULL 的列

SELECT Country, InternetUse, Year
FROM EconomicIndicators
WHERE InternetUse IS NOT NULL

+-------------------+-------------------+------------+
|Country            |InternetUse        |Year        |
|-------------------+-------------------+------------+
|Afghanistan        |4.58066992         |2011        |
|Albania            |49                 |2011        |
|Algeria            |14                 |2011        |
....
+-------------------+-------------------+------------+

T-SQL：偵測 NULL

SELECT  Country, InternetUse, Year
FROM EconomicIndicators
WHERE InternetUse IS NULL

+-------------------+-------------------+------------+
|Country            |InternetUse        |Year        |
|-------------------+-------------------+------------+
|Angola             |NULL               |2013        |
|Argentina          |NULL               |2013        |
|Armenia            |NULL               |2013        |
....
+-------------------+-------------------+------------+

空白不等於 NULL

空白不等於 NULL
常見於文字欄位
可用空字串 '' 尋找空白值
最佳作法：找出長度（Length 或 LEN）> 0 的欄位

空白不等於 NULL

SELECT Country, GDP, Year
FROM EconomicIndicators
WHERE LEN(GDP) > 0

+-------------------+-------------------+------------+
|Country            |GDP                |Year        |
|-------------------+-------------------+------------+
|Afghanistan        |54852215624        |2011        |
|Albania            |29334492905        |2011        |
|Algeria            |453558093404       |2011        |
....
+-------------------+-------------------+------------+

用 ISNULL 以特定值取代遺漏值

SELECT GDP, Country, 
ISNULL(Country, 'Unknown') AS NewCountry
FROM EconomicIndicators

+-------------------+----------------+----------------+
|GDP                |Country         |NewCountry      |
|-------------------+----------------+----------------+
|5867920022         |NULL            |Unknown         |
|597873038497       |South Africa    |South Africa    |  
|1474091271101      |NULL            |Unknown         |
...
+-------------------+----------------+----------------+

用 ISNULL 以其他欄位取代遺漏值

/*Substituting values from one column for another with ISNULL*/
SELECT TradeGDPPercent, ImportGoodPercent,
ISNULL(TradeGDPPercent, ImportGoodPercent) AS NewPercent
FROM EconomicIndicators

+-------------------+------------------+----------------+
|TradeGDPPercent    |ImportGoodPercent |NewPercent      |
|-------------------+------------------+----------------+
|NULL               |56.7              |56.7            |
|52.18720739        |51.75273421       |52.18720739     |  
|NULL               |NULL              |NULL            |
...
+-------------------+------------------+----------------+

用 COALESCE 取代 NULL 值

COALESCE 會回傳第一個非遺漏值

COALESCE( value_1, value_2, value_3, ... value_n )

若 value_1 為 NULL 而 value_2 非 NULL，則回傳 value_2
若 value_1 與 value_2 為 NULL，而 value_3 非 NULL，則回傳 value_3
...

使用 COALESCE 的 SQL 範例

SELECT TradeGDPPercent, ImportGoodPercent,
COALESCE(TradeGDPPercent, ImportGoodPercent, 'N/A') AS NewPercent
FROM EconomicIndicators

+-------------------+--------------------+---------------+
|TradeGDPPercent    |ImportGoodPercent   |NewPercent     |
|-------------------+--------------------+---------------+
|NULL               |56.7                |56.7           |
|NULL               |NULL                |N/A            |
|52.18720739        |51.75273421         |52.18720739    |
+-------------------+--------------------+---------------+

一起來練習吧！

SQL Server 中級