Nulls

Null values are values in a record that have no data. Before we talk about nulls it might be beneficial to talk about empty strings and zeros to see what null values are not.

An empty string is is CHAR or VARCHAR character string with a value of ''. (That is two single quote marks with nothing in between.) This is different than a null because a value has been set. It just has been set to a empty string.

A zero is a numeric value that represents nothing. You probably learned this in elementary school. It is not equivalent to null. A null value in a numeric column indicates that the value has not been set. A zero indicates the value has been set to nothing.

Null values are values that have not been set. There is just nothing there. Why would we want that? I comes in handy for seeing missing data. Consider the award table. If we are awarding students we would create a record with an awarded value. But we could leave the accepted and paid amounts null. When the student accepts the award the awarded amount would be set. If we query for records where the awarded amount is null we create a list of awards that need to be accepted. If we search for awards that are not null we get a list of awards that have been accepted. (Remember though, the award may have been accepted with a value of zero.)

We can find null records with the keywords IS NULL and IS NOT NULL in our WHERE statement.

IS NULL

SELECT * FROM award
WHERE award_accepted IS NULL;

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00009	SEOG	90.0
X00010	PELL	100.0
X00010	SEOG	110.0

IS NOT NULL

By adding the keyword NOT we can negate the selection. For each record the database will determine if the value is null. Because of the NOT keyword it will change true to false and vice versa. The example SQL below will select everything in the award table except the three records in the above example.

SELECT * FROM award
WHERE award_accepted IS NULL;

COALESCE()

COALESCE() is a function that allows you to select between two columns for your query's output. It takes two or more column names as arguments. If the first column has a null value COALESCE() will choose the second column. If you provide more than three columns it will evaluate each column from left to right and use the first value that is not null. If all of them are null it will use the last column's value.

Instead of providing a column name you can provide a literal value such as a string or number. Since the literal value is not null it will always be used returned by COALESCE(). The common usage is to make the last argument a literal default value.

SELECT
	a.award_id,
	a.award_fund,
	COALESCE(a.award_offered, 0) AS offered,
	COALESCE(a.award_accepted, 0) AS accepted,
	COALESCE(a.award_disbursed, 0) AS disbursed
FROM award AS a
WHERE award_accepted IS NULL;

award_id	award_fund	offered	accepted	disbursed
X00009	SEOG	90.0	0	0
X00010	PELL	100.0	0	0
X00010	SEOG	110.0	0	0

This example selects the three records with null accepted amounts. It uses COALESCE() to select the column value but if the value is null it will instead select a literal zero. This neatly replaces nulls with zeros in our output. This can be very important if our output will be imported into a spreadsheet program. Spreadsheets tend to have problems with null cells. If you try to autofill a column and it hits a cell with a null value it will silently fail. It can be quite annoying for you and your end users. Santizing your output with COALESCE() can fix that.

But COALESCE() can select from any number of values before it finds one that is not null. In the example below it checks the disbursed, accepted, and offered amounts in that order. If it can't find a non-null value the final option is a numeric zero. Because the output's column could contain data from three different input columns we rename it to "amount".

SELECT
	a.award_id,
	a.award_fund,
	COALESCE(a.award_disbursed, a.award_accepted, a.award_offered) AS amount
FROM award AS a
WHERE a.award_accepted IS NULL
OR a.award_disbursed IS NULL;

award_id	award_fund	amount
X00002	SEOG	20.0
X00009	SEOG	90.0
X00010	PELL	100.0
X00010	SEOG	110.0

Gosh, gee whiz

The last example is really cool and all, but the output is confusing. The "amount" column could have the award's offered, accepted, or disbursed amount. Or it could just have a default zero. It would be really swell if we could add a column indicating the award's status. Well, it turns out we can do that with case statements.

Golly!