{"id":174,"date":"2018-08-11T18:00:44","date_gmt":"2018-08-11T18:00:44","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=174"},"modified":"2018-07-12T20:02:08","modified_gmt":"2018-07-12T20:02:08","slug":"managing-update-differences-null-and-not-null","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/","title":{"rendered":"Managing update differences – NULL and NOT NULL"},"content":{"rendered":"

When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.<\/p>\n

The Problem<\/h1>\n

Equality (or lack thereof) between values is an easy check. Is 1 = 3<\/code>, or is 4 <> 5<\/code>? Many people (and processes) get unexpected results when they try to compare NULL<\/code> with NULL<\/code>.<\/p>\n

If we consider the below dataset. It contains rows without NULLs, and rows with one or two NULLs:<\/p>\n

--------------------------------------------------------------------------------\r\n-- Create Dataset\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE [value_list]\r\n(\r\n    [ID]     INT NOT NULL PRIMARY KEY,\r\n    [val_1]  INT NULL,\r\n    [val_2]  INT NULL\r\n);\r\n\r\nINSERT\r\n  INTO [value_list]\r\n      ([ID], [val_1], [val_2])\r\nVALUES(   1,       1,       1),\r\n      (   2,       2,       2),\r\n      (   3,       3,    NULL),\r\n      (   4,    NULL,    NULL),\r\n      (   5,    NULL,       5),\r\n      (   6,       6,       7)\r\n;\r\n<\/pre>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t
ID<\/th>val_1<\/th>val_2<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>1<\/td>1<\/td>\n<\/tr>\n
2<\/td>2<\/td>2<\/td>\n<\/tr>\n
3<\/td>3<\/td>NULL<\/td>\n<\/tr>\n
4<\/td>NULL<\/td>NULL<\/td>\n<\/tr>\n
5<\/td>NULL<\/td>5<\/td>\n<\/tr>\n
6<\/td>6<\/td>7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
Equality (or not)<\/h2>\n
Rows 1 & 2 will be returned for a test of equality:<\/p>\n
SELECT [ID],\r\n       [val_1],\r\n       [val_2]\r\n  FROM [value_list]\r\n WHERE [val_1] = [val_2]\r\n;\r\n\r\n\/*\r\nID , val_1 , val_2\r\n1 , 1 , 1\r\n2 , 2 , 2\r\n*\/<\/pre>\n
Similarly row 6 will be returned for a test of inequality:<\/p>\n
SELECT [ID],\r\n       [val_1],\r\n       [val_2]\r\n  FROM [value_list]\r\n WHERE [val_1] <> [val_2]\r\n;\r\n\r\n\/*\r\nID  , val_1 , val_2\r\n6   , 6     , 7\r\n*\/<\/pre>\n
But what about the NULLs?<\/h2>\n
NULL always has been, and always will be special. We need it for many good reasons. But that doesn’t stop it being a potential oversight when we consider the two scenarios:<\/p>\n
    \n
  1. For the equality check, what if we want to include the NULL values? Afterall, if both val_1 and val_2 are NULL, aren’t they the same?<\/li>\n
  2. And for the difference check, isn’t NULL a different value to 3 (as in row 3) or 5 (as in row 5)?<\/li>\n<\/ol>\n
    The solutions<\/h1>\n
    Removing NULLs from the data<\/h2>\n
    Simply make the columns NOT NULL, and this problem won’t happen. If you don’t have a value, then stick something in there for UNKNOWN (-1 for integer references, an empty string etc…).<\/p>\n
    While this can be done, it’s not best practice, and can have it’s own issues if the predefined value suddenly has a meaning in the data.<\/p>\n
    COALESCE to an arbitrary value<\/h2>\n
    This is the easiest way to perform the check, modify both sides of the check to have a default (arbitrary) value to replace NULL with. When there is a NULL, it will be substituted:<\/p>\n
    SELECT [ID],\r\n       [val_1],\r\n       [val_2]\r\n  FROM [value_list]\r\n WHERE COALESCE([val_1], -1) <> COALESCE([val_2], -1)\r\n;\r\n\r\n\/*\r\nID  , val_1 , val_2\r\n3   , 3     , NULL\r\n5   , NULL  , 5\r\n6   , 6     , 7\r\n*\/<\/pre>\n
    This gets round the issue of NULL comparissons, but it has two drawbacks:<\/p>\n
      \n
    1. One has to think of a value which won’t appear in the data (with numbers a negative can be used, but not always).<\/li>\n
    2. The value has to change depending on the datatype.<\/li>\n<\/ol>\n
      Multiple Checks<\/h2>\n
      SELECT [ID],\r\n       [val_1],\r\n       [val_2]\r\n  FROM [value_list]\r\n WHERE(   [val_1] <> [val_2]\r\n       OR([val_1] IS     NULL AND [val_2] IS NOT NULL)\r\n       OR([val_1] IS NOT NULL AND [val_2] IS     NULL)\r\n      )\r\n;\r\n\r\n\/*\r\nID  , val_1 , val_2\r\n3   , 3     , NULL\r\n5   , NULL  , 5\r\n6   , 6     , 7\r\n*\/<\/pre>\n
      The above is my preferred method, it’s longer in code than using COALESCE but it’s the same code for all datatypes and explicit in all cases. It also avoids the challenge of thinking of a value that is (in theory) impossible to appear in the actual data.<\/p>\n
      See working solution on\u00a0rextester.com<\/a><\/p>\n
      The Dream Solution<\/h1>\n
      My dream (which doesn’t exist) is a modifier for statements, which would allow NULL to behave like a distinct value:<\/p>\n
      -- Different values\r\nSELECT *\r\n  FROM t_1\r\n WHERE c_1 <x> c_2\r\n;\r\n\r\n\r\n-- Equality\r\nSELECT\u00a0*\r\n \u00a0FROM\u00a0t_1\r\n\u00a0WHERE\u00a0c_1\u00a0x=\u00a0c_2\r\n;<\/pre>\n
       <\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method. The Problem Equality (or lack thereof) between values is an easy check. Is 1 = 3, or is 4 <> 5? Many people (and processes) get unexpected results when they try to…<\/p>\n
      Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[17],"class_list":["post-174","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-null"],"yoast_head":"\nManaging update differences - NULL and NOT NULL - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Managing update differences - NULL and NOT NULL - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-11T18:00:44+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Managing update differences – NULL and NOT NULL\",\"datePublished\":\"2018-08-11T18:00:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/\"},\"wordCount\":421,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"NULL\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/\",\"name\":\"Managing update differences - NULL and NOT NULL - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-08-11T18:00:44+00:00\",\"description\":\"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/08\\\/11\\\/managing-update-differences-null-and-not-null\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Managing update differences – NULL and NOT NULL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Managing update differences - NULL and NOT NULL - Rows Across The Lake","description":"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/","og_locale":"en_GB","og_type":"article","og_title":"Managing update differences - NULL and NOT NULL - Rows Across The Lake","og_description":"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-08-11T18:00:44+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Managing update differences – NULL and NOT NULL","datePublished":"2018-08-11T18:00:44+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/"},"wordCount":421,"commentCount":2,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["NULL"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/","name":"Managing update differences - NULL and NOT NULL - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-08-11T18:00:44+00:00","description":"When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/11\/managing-update-differences-null-and-not-null\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Managing update differences – NULL and NOT NULL"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=174"}],"version-history":[{"count":6,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/174\/revisions"}],"predecessor-version":[{"id":188,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/174\/revisions\/188"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}