Finding and Resolving Missing Data

Intermediate SQL Server

Ginger Grant

Instructor

Detecting missing values

When you have no data, the empty database field contains the word NULL
Because NULL is not a number, it is not possible to use =, <, or > to find or compare missing values
To determine if a column contains a NULL value, use IS NULL and IS NOT NULL

Returning No NULL Values in 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        |
....
+-------------------+-------------------+------------+

Detecting NULLs in T-SQL

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

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

Blank is not NULL

A blank is not the same as a NULL value
May show up in columns containing text
An empty string '' can be used to find blank values
The best way is to look for a column where the Length or LEN > 0

Blank is not NULL

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

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

Substituting missing data with a specific value using 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         |
...
+-------------------+----------------+----------------+

Substituting missing data with a column using 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            |
...
+-------------------+------------------+----------------+

Substituting NULL values using COALESCE

COALESCE returns the first non-missing value

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

If value_1 is NULL and value_2 is not NULL, return value_2
If value_1 and value_2 are NULL and value_3 is not NULL, return value_3
...

SQL Statement using 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    |
+-------------------+--------------------+---------------+

Let's practice!

Intermediate SQL Server