{"id":3490,"date":"2012-12-23T03:49:06","date_gmt":"2012-12-23T03:49:06","guid":{"rendered":"http:\/\/craftydba.com\/?p=3490"},"modified":"2012-12-24T03:02:32","modified_gmt":"2012-12-24T03:02:32","slug":"basic-training-all-about-null","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3490","title":{"rendered":"Basic Training – All About NULL"},"content":{"rendered":"

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

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<\/a>. <\/p>\n

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. <\/p>\n

The TSQL snippet below accomplishes these actions.<\/p>\n

--\r\n-- Select training database\r\n--\r\n\r\n-- Which database to use.\r\nUSE [TRAINING]\r\nGO\r\n\r\n\r\n--\r\n-- Create publishers table \r\n--\r\n\r\n-- Delete existing table\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects\r\n    WHERE object_id = OBJECT_ID(N'[PUBS].[PUBLISHERS]') AND type = 'U'\r\nDROP TABLE [PUBS].[PUBLISHERS]\r\nGO\r\n\r\n-- Create new table\r\nCREATE TABLE [PUBS].[PUBLISHERS]\r\n(\r\n   pub_id char(4) NOT NULL,\r\n   pub_name varchar(40) NULL,\r\n   city varchar(20) NULL,\r\n   state char(2) NULL,\r\n   country varchar(30) NULL\r\n)\r\nGO\r\n\r\n<\/span><\/pre>\n
What use is a table without some data? The first TSQL statement fails due to violating the NOT NULL constraint.<\/p>\n
-- Try a null publisher id\r\nINSERT [PUBS].[PUBLISHERS] VALUES (NULL, 'New Moon Books', 'Boston', 'MA', 'USA')\r\n\r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
The code below inserts 5 rows into our newly built table.  Every column other than the [pub_id] accepts NULL values.<\/p>\n
-- Add five records\r\nINSERT [PUBS].[PUBLISHERS] VALUES ('0736', NULL, NULL, NULL, NULL)\r\nINSERT [PUBS].[PUBLISHERS] VALUES ('0877', 'Binnet & Hardley', 'Washington', 'DC', 'USA')\r\nINSERT [PUBS].[PUBLISHERS] VALUES ('1389', 'Algodata Infosystems', 'Berkeley', 'CA', 'USA')\r\nINSERT [PUBS].[PUBLISHERS] VALUES ('9952', 'Scootney Books', 'New York', 'NY', 'USA')\r\nINSERT [PUBS].[PUBLISHERS] VALUES ('1622', 'Five Lakes Publishing', 'Chicago', 'IL', 'USA')\r\n\r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
On very important fact that most new developers miss is that anything compared to<\/a> 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’.  <\/p>\n
How do we select that NULL row?  We need to use the IS NULL<\/a> expression to return the row with a NULL value. <\/p>\n
-- Skips null row\r\nSELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] <> 'DC'\r\n\r\n-- Shows one row\r\nSELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] = 'DC'\r\n\r\n-- Find row with null\r\nSELECT * FROM [PUBS].[PUBLISHERS] WHERE [state] IS NULL  \r\n\r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
Null Examples<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[64,63,31,12,682,681,15,679,680,29],"class_list":["post-3490","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-table","tag-create-table","tag-database-developer","tag-free-code","tag-is-not-null","tag-is-null","tag-john-f-miner-iii","tag-not-null","tag-null","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3490"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3490\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}