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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<span style="color: #008000; font-size: small;">-- -- Select training database -- -- Which database to use. USE [TRAINING] GO -- -- Create publishers table -- -- Delete existing table IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PUBS].[PUBLISHERS]') AND type = 'U' DROP TABLE [PUBS].[PUBLISHERS] GO -- Create new table CREATE TABLE [PUBS].[PUBLISHERS] ( pub_id char(4) NOT NULL, pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL ) GO </span> |
What use is a table without some data? The first TSQL statement fails due to violating the NOT NULL constraint.
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Try a null publisher id INSERT [PUBS].[PUBLISHERS] VALUES (NULL, 'New Moon Books', 'Boston', 'MA', 'USA') </span> |
The code below inserts 5 rows into our newly built table. Every column other than the [pub_id] accepts NULL values.
1 2 3 4 5 6 7 8 |
<span style="color: #008000; font-size: small;">-- Add five records INSERT [PUBS].[PUBLISHERS] VALUES ('0736', NULL, NULL, NULL, NULL) INSERT [PUBS].[PUBLISHERS] VALUES ('0877', 'Binnet & Hardley', 'Washington', 'DC', 'USA') INSERT [PUBS].[PUBLISHERS] VALUES ('1389', 'Algodata Infosystems', 'Berkeley', 'CA', 'USA') INSERT [PUBS].[PUBLISHERS] VALUES ('9952', 'Scootney Books', 'New York', 'NY', 'USA') INSERT [PUBS].[PUBLISHERS] VALUES ('1622', 'Five Lakes Publishing', 'Chicago', 'IL', 'USA') </span> |
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.
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #008000; font-size: small;">-- Skips null row SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] <> 'DC' -- Shows one row SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] = 'DC' -- Find row with null SELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] IS NULL </span> |
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.