Basic Training – All About NULL

Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING.

The most basic part of a database is a TABLE which consists of COLUMNS. After choosing the data type for the COLUMN, we need to decide if the value is optional for some of the data rows. If it is, we need to define the column as NULL, which is the default if not explicitly defined. If we want the column to have a value for every row, we need to define the column as NOT NULL.

Today, we are going to build upon the sample database named [TRAINING] that contains a sample schema named [PUBS]. The [PUBLISHERS] table is going to be created with the [pub_id] field as NOT NULL and all other as NULL.

The TSQL snippet below accomplishes these actions.

What use is a table without some data? The first TSQL statement fails due to violating the NOT NULL constraint.

The code below inserts 5 rows into our newly built table. Every column other than the [pub_id] accepts NULL values.

On very important fact that most new developers miss is that anything compared to or calculated with NULL is NULL (UNKNOWN). Logically, the first statement should return 4 publishers which are not in ‘DC’ instead of 3. However, it contains a NULL value. The second statement return an exact match on 1 publisher that is located in ‘DC’.

How do we select that NULL row? We need to use the IS NULL expression to return the row with a NULL value.

To reiterate the main points of this talk, use the NOT NULL constraint to require data entry for important fields like primary keys, natural keys, foreign keys, etc. If a field is optional, skip the NULL clause since it is the default. Do not forget that records with NULL values drop out of the results. Use the IS NULL predicate to find those pesky records.

Null Examples

Related posts

Leave a Comment