|
|
The final topic you're going to look at in this chapter is how to deal with columns that contain NULL values—that is, columns that don't contain a value at all. This may occur by design but may also be the result of an error, such as a failure to enter the appropriate data into the database.
In general, RDBMSs allow you to specify whether columns allow NULL values, which can make your life easier. If you say that a column can't contain a NULL value, then an attempt to add a row without specifying a value for that column will result in an error. Furthermore, for tables that allow NULL values in one or more columns, then you can't always rely on values being present when you perform SELECT queries. Where NULL values are allowed, you must be prepared to deal with them in your code; otherwise, unpredicted errors could occur.
You can use the NULL SQL keyword to test for NULL values:
SELECT ColumnA, ColumnB FROM Table WHERE ColumnC IS NOT NULL;
Similarly, you could select only those rows with NULL values:
SELECT ColumnA, ColumnB FROM Table WHERE ColumnC IS NULL;
Enforcing a non-null condition can be useful. For example, in the Exam table in the InstantUniversity database, the Comments column contains a couple of NULL values. Suppose you wanted to extract a list of only rows with no comments. You could use the following query:
SELECT ExamID, CourseID, ProfessorID, SustainedOn FROM Exam WHERE Comments IS NULL;
This query produces the following results:
ExamID CourseID ProfessorID SustainedOn ----------- ----------- ------------ ----------- 4 4 3 03/18/2003 6 6 3 03/25/2003
|
|