SQL Tutorial : Finding and Resolving Missing Data

In this lesson, you are going to look at how to identify and resolve missing data. Missing data is a common occurrence in SQL. Missing data is represented as null. Since null is not a value, you cannot use the equal sign or any other operators to find or compare nulls. Instead, you must use isnull and is not null to check if there is a missing value in a column, you can use is not null. For example, to return all rows without any missing values in Internet use, you can add the clause where Internet use is not null as shown here on the last line of the query. To return all the rows where Internet use is null, use the where internetuseisnull statement as shown here in this query. Note that there is a difference between a blank value and a null value. Sometimes missing values may be formatted as blanks. A blank is two single quotes with no space in between and is only found in a text field. An empty row value will show up as a blank in your query. It does not contain a value, but it will not show up as null. The best way to find if a column contains a blank value is look for a column where the length which is represented as len is greater than zero. As a blank value is a field with nothing in it. You can exclude blank values by returning rows where the length of the field is greater than zero. To do this, you can use a lend function, the left parenthesis and the right parenthesis as shown here, to only return columns which have a character length greater than zero on the relevant column. Sometimes you want to replace missing values in a column with a specific value. To do so, you can use the isnull function. You pass the column name and the value to replace isnull and this will replace the missing values in the column with the given value as shown here. Note that you can replace all missing values with only one value. You can also use isnul to replace missing values in one column based on a different column. Here we are replacing all the values in trade GDP percentage with the corresponding row values from importbutpercent. The replacement will only be performed if the value in trade gdpp percent is null. Another way to replace null values is to use the coalesce statement. You can pass multiple values to coalesce and it will always return the first non null value. It is similar to using a series of if then else statements to return the first non missing value. Here's a query to show how coalesce works for the first row. Since the value in trade gdp percent is null and the value in import good percent is not null new percent is given the value from the import good percent column. For the second row, both trade GDP percent and import good percent are null. So new percent column it has resigned the values na. Finally, in the third row, both trade GDP percent and import good percent are not null. So the first not null value, the value from trade gdprcent column is assigned to the column new percent. Time to put this.