Menemukan dan Menangani Data Hilang

SQL Server Menengah

Ginger Grant

Instructor

Mendeteksi nilai hilang

Saat tidak ada data, kolom basis data kosong berisi kata NULL
Karena NULL bukan angka, Anda tidak bisa memakai =, <, atau > untuk mencari atau membandingkan nilai yang hilang
Untuk memeriksa apakah kolom berisi nilai NULL, gunakan IS NULL dan IS NOT NULL

Mengembalikan tanpa nilai NULL di T-SQL

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

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

Mendeteksi NULL di T-SQL

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

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

Kosong bukan NULL

Kosong tidak sama dengan nilai NULL
Sering muncul pada kolom teks
String kosong '' dapat dipakai untuk mencari nilai kosong
Cara terbaik adalah mencari kolom dengan Length atau LEN > 0

Kosong bukan NULL

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

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

Mengganti data hilang dengan nilai tertentu menggunakan 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         |
...
+-------------------+----------------+----------------+

Mengganti data hilang dengan kolom menggunakan 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            |
...
+-------------------+------------------+----------------+

Mengganti nilai NULL menggunakan COALESCE

COALESCE mengembalikan nilai pertama yang tidak hilang

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

Jika value_1 adalah NULL dan value_2 bukan NULL, kembalikan value_2
Jika value_1 dan value_2 NULL dan value_3 bukan NULL, kembalikan value_3
...

Pernyataan SQL menggunakan COALESCE

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    |
+-------------------+--------------------+---------------+

Ayo berlatih!

SQL Server Menengah