{"id":82836,"date":"2019-01-10T16:22:05","date_gmt":"2019-01-10T16:22:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=82836"},"modified":"2021-06-03T16:48:19","modified_gmt":"2021-06-03T16:48:19","slug":"empty-thoughts-working-with-null","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/empty-thoughts-working-with-null\/","title":{"rendered":"Empty Thoughts: Working with NULL"},"content":{"rendered":"

One of the hardest concepts in learning SQL is the meaning of a NULL. Traditionally, programming languages had no concept of missing or UNKNOWN<\/em> data. The closest example that most programmers ran into was the \u2018not applicable\u2019 flags in spreadsheets, or the classic TBD<\/em> flags for undetermined instructors, locations, or other things in print outs.<\/p>\n

Dr. Codd defined a NULL as the lack of a value, so talking about NULL values<\/em> is wrong. In SQL, however, we have to worry about physical<\/em> storage. This means we have to know the data type of the column which is holding the NULL so the compiler can do its job. From that requirement, it logically follows that we can write CAST(NULL AS < data type>)<\/code> and not just depend on automatic type conversions.<\/p>\n

SQL is notorious for its three-value logic {TRUE, FALSE, UNKNOWN}<\/code> which results from trying to compare a NULL to something, including another NULL. Since it is not a value, NULL <> NULL<\/code> is UNKNOWN<\/em>, but likewise so is NULL = NULL<\/em>! This why we have the predicate <expression> IS [NOT] NULL<\/code> to check for a NULL. This predicate and the EXISTS()<\/code>function are some of the few predicates in SQL that can only return {TRUE, FALSE}.<\/p>\n

-- Returns 'NULL IS NULL\u2019\r\nIF NULL IS NULL\r\n PRINT 'NULL IS NULL'\r\nELSE\r\n PRINT 'NULL IS NOT NULL';<\/pre>\n
NULLs in DDL<\/h2>\n
SQL defines a PRIMARY KEY(<column list>)<\/code> as being implicitly declared NOT NULL<\/em>. It is probably a good idea to go ahead and put the NOT NULL<\/em> in your table declarations anyway. If the PRIMARY KEY constraint changes, then you are still safe. According to Dr. Codd and relational theory, to be a real table it must have a key. Originally, Dr. Codd said a PRIMARY KEY had to be designated, but later realized that a key is a key, so there\u2019s no need to designate something special about one of them. This was another leftover from trying to implement relational systems on top of old file systems. In file systems, records (which are nothing like rows) come in a linear search sequence in physical storage, so tapes had to be sorted. The original sort key became the PRIMARY KEY in the new SQL products. Random access on unsorted magnetic tapes is technically possible, but it really doesn\u2019t work.<\/p>\n
But the relational model accommodates multiple keys in the same table. The syntax we picked for non- primary keys was a little strange. We added the UNIQUE(<column list>)<\/code> constraint which guarantees that all the rows in the table will be different. It also allows more than one column, but the columns involved can have NULLs. Remember that in the relational model, a key can\u2019t have NULLs, But you\u2019re only allowed to have one NULL-ed row, as if it were a value.<\/p>\n
All of these strange rules come from the GROUP BY<\/code> clause. Without lapsing into a college algebra lesson, we have two equivalence relations, as they are called in set theory. The first is just regular old equals (=) with the extra rules about NULLs. The second relation is the GROUP BY<\/code>, in which all the NULLs are put into one equivalence class.<\/p>\n
CREATE TABLE Foobar\r\n(foo_id CHAR(5) NOT NULL PRIMARY KEY, \r\n a1 INTEGER, \r\n a2 INTEGER, \r\n a3 INTEGER, \r\nCHECK ((a1 +a2 +a3) < 10)\r\n);\r\nINSERT INTO Foobar VALUES ('test1', 1, 1, 1); --- works!\r\nINSERT INTO Foobar VALUES ('test2', 10, 10, 10); --- fails!\r\nINSERT INTO Foobar VALUES ('test3', NULL, 1, 1); --- works!<\/pre>\n
However, in the DDL, the UNKNOWN<\/em> result of the search condition in a CHECK(<search condition>)<\/code> constraint is given the \u2018benefit of the doubt\u2019 and treated the same as TRUE<\/em>.<\/p>\n
NULLs in DML<\/h2>\n
When you use it in the DML statements, it treats the UNKNOWN<\/em> result the same as a FALSE<\/em>. Query updates and inserts have a stronger criterion.<\/p>\n
SELECT foo_id\r\n FROM Foobar\r\nWHERE (a1 +a2 +a3) < 10;<\/pre>\n
returns only test1<\/em> as a result. The test2<\/em> row failed to insert. The test3<\/em> row became<\/p>\n
\u201cWHERE (NULL + 1 +1) < 10\u201d<\/p>\n
\u201cWHERE (NULL + 2) < 10\u201d<\/p>\n
\u201cWHERE NULL < 10\u201d<\/p>\n
\u201cWHERE UNKNOWN\u201d or \u201cWHERE FALSE\u201d<\/p>\n
Replacing NULLs with actual values is very often handy. The SQL Server\/Sybase family originally had the function ISNULL(<expression>, <non-NULL value>)<\/code>. If the <expression><\/code> was NULL then it returns the <value><\/code>. The data type of the result is taken from the first parameter. That particular choice can make for some funny results when parameters are not the same data types.<\/p>\n
Microsoft now has the ANSI\/ISO Standard COALESCE(<expression list>)<\/code>. The first thing to notice is that COALESCE<\/code> takes a list<\/em> of expressions, which it then parses left to right to determine the highest data type in the list, which becomes the data type of the result. The list is then parsed again from left to right to find the first non-NULL value, which is returned and cast to the result data type. One common mistake beginners make with this is to put a higher data type in the list when it\u2019s not what they really meant.<\/p>\n
I\u2019m often asked why we chose the word \u201ccoalesce\u201d instead of something else. We were trying to come up with the word that would be descriptive but not so common that it might be misunderstood or used as a column name. Phil Shaw of IBM pulled out a pocket thesaurus and started going down synonyms until he came up with this.<\/p>\n
Grouping NULLs<\/h2>\n
A common way to use the grouping relation to return TRUE<\/em> if two expressions are both NULL or their values match:<\/p>\n
CASE WHEN \r\n COALESCE(foobar, 'weird value that does not occur in the database') \r\n = COALESCE(barfoo, 'weird value that does not occur in the database')\r\n THEN 0\r\n ELSE 1\r\n END = 0<\/pre>\n
What if there is no \u2018weird value\u2019 you can use for the COALESCE check? You just need to add more search conditions:<\/p>\n
CASE WHEN (foo = bar OR (foo IS NULL AND bar IS NULL))\r\n THEN 0\r\n ELSE 1\r\n END = 0<\/pre>\n
While SQL Server does not yet have this construct, the SQL Standards added another comparison operator for this problem. This feature was introduced in two steps: SQL:1999 added T151, DISTINCT<\/code> predicate. The optional negation with NOT<\/code> was added by SQL:2003 as feature T152, DISTINCT<\/code> predicate with negation.<\/p>\n
<expression> IS [NOT] DISTINCT FROM <expression><\/pre>\n
Note that you have to use the negated form to get the results you want. The un-negated form is not really equality. This is easy to see with a truth table:<\/p>\n\n\n\n\n\n\n\n
\n
A<\/p>\n<\/td>\n
\n
B<\/p>\n<\/td>\n
\n
A = B<\/p>\n<\/td>\n
\n
A IS NOT DISTINCT FROM B<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
TRUE<\/p>\n<\/td>\n
\n
TRUE<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
FALSE<\/p>\n<\/td>\n
\n
FALSE<\/p>\n<\/td>\n<\/tr>\n
\n
0<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
UNKNOWN<\/p>\n<\/td>\n
\n
FALSE<\/p>\n<\/td>\n<\/tr>\n
\n
NULL<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
UNKNOWN<\/p>\n<\/td>\n
\n
TRUE<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Set Operators and NULLs<\/h2>\n
SQL has set operators (UNION<\/code>, INTERSECT<\/code>, EXCEPT<\/code>) which work on table expressions. Both tables have to be what we call \u2018union compatible,\u2019 which means the tables have the same structure (the corresponding columns in each table are in the same order and have compatible datatypes) , and the result will have that structure. Most people don\u2019t know that technically the result table does not have a name nor do the columns unless you actually assigned them with a <set expression> AS <table name> (column name list>)<\/code> construct.<\/p>\n
Set operators discard the duplicate rows and use the grouping rather than the equality relationship to discard multiple NULLs as well as duplicate values. The EXCEPT<\/code> and INTERSECT<\/code> operators also work in this way.<\/p>\n
CREATE TABLE T1 (x INTEGER);\r\n INSERT INTO T1 VALUES (NULL);\r\nCREATE TABLE T2 (y INTEGER);\r\n INSERT INTO T2 VALUES (NULL);\r\n-- Returns one NULL result \r\n SELECT * FROM T1 \r\nUNION \r\nSELECT * FROM T2;\r\n-- Returns one NULL result \r\nSELECT * FROM T1 \r\nINTERSECT \r\nSELECT * FROM T2;\r\n-- Returns an empty table\r\nSELECT * FROM T1 \r\nEXCEPT \r\nSELECT * FROM T2;<\/pre>\n
OUTER JOIN and NULLs<\/h2>\n
OUTER<\/code> JOINs<\/code> come in three flavors (LEFT<\/code>, RIGHT<\/code>, FULL<\/code>) and were designed to solve an actual problem. Before the SQL-99 Standard, there was no standardized syntax for them nor a Standard definition of how they would work. Sybase in SQL Server did it one way and Oracle did it another way, then there was a product from a company called Gupta Technologies LLC that let you pick which one you wanted to use. I\u2019m going to assume that everyone knows how an outer join works. The \u2018preserved table\u2019 is the one on the left (or right or both) side of the join operator, and the unpreserved table, if any, is the one on the opposite side. Since all datatypes in SQL must be NULLable, the values that did not match the join condition in the preserved table can be padded out with NULLs. It doesn\u2019t matter if the original columns in the preserved table were declared NOT<\/code> NULL<\/code> because the result of the join is technically a whole new table.<\/p>\n
OLAP and NULLs<\/h2>\n
When OLAP (online analytical processing) databases first came in, ANSI responded by defining some basic hierarchical aggregations in ANSI\/ISO Standard SQL-99. They are defined as extensions to the GROUP<\/code> BY<\/code> clause. The original three were GROUPING<\/code> SET<\/code>, ROLLUP<\/code> and CUBE<\/code>. The last two are defined in the standard using the GROUPING<\/code> SET<\/code> construct. Rollup and cube are often called \u2018super groups\u2019 because they could be defined using the regular GROUP<\/code> BY<\/code> and UNION<\/code> operators. As expected, the NULLs form their own group. However, we now have a special function, GROUPING<\/code> (<column reference>)<\/code>, which returns a one if the column was created by the operation or zero otherwise. Then SQL-2003 added a multicolumn version, GROUPING<\/code>_ID<\/code>, that constructs a binary number from the zeros and ones in the columns in the list, using an implementation defined exact numeric data type; this is handy as you think.<\/p>\n
These constructs allow you to do what we used to call \u2018control break reports\u2019 back in the pre-SQL days. You put a sequential file in sorted order and pass it through a program that would keep running totals in accumulators (an old term that actually used to refer to a physical feature in unit record equipment). When the controls (the columns at various levels in the hierarchy) changed, the accumulators were printed out and reset. Most of the time, frankly, this was doing running totals.<\/p>\n
This is probably easier to see with an example. Imagine we have a file that gives us a region number and the city name along with the total sales for that city. We want to get a report that shows us the totals by region, the totals by city within the regions, and finally, a grand total for all sales in the company.<\/p>\n
SELECT region_nbr, city_name, SUM(sale_amt) AS sales_tot\r\n FROM Regional_Sales\r\nGROUP BY ROLLUP (region_nbr, city_name)\r\nORDER BY region_nbr, city_name; <\/pre>\n
That\u2019s assuming that there is some sample data, the output might look like this (the right-hand most column is a comment not part of the output)<\/p>\n
 <\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
…<\/p>\n<\/td>\n
\n
\u2026<\/p>\n<\/td>\n
\n
\u2026<\/p>\n<\/td>\n
\n
comment<\/p>\n<\/td>\n<\/tr>\n
\n
‘006’<\/p>\n<\/td>\n
\n
\u2018Austin\u2019<\/p>\n<\/td>\n
\n
500.13<\/p>\n<\/td>\n
\n
\uf0ac\uf020city within region total<\/p>\n<\/td>\n<\/tr>\n
\n
‘006’<\/p>\n<\/td>\n
\n
\u2018Dallas\u2019<\/p>\n<\/td>\n
\n
2010060.5<\/p>\n<\/td>\n
 <\/td>\n<\/tr>\n
\n
‘006’<\/p>\n<\/td>\n
\n
\u2018San Antonio\u2019<\/p>\n<\/td>\n
\n
475.01<\/p>\n<\/td>\n
 <\/td>\n<\/tr>\n
\n
\u2026<\/p>\n<\/td>\n
\n
…<\/p>\n<\/td>\n
\n
\u2026<\/p>\n<\/td>\n
\n
\u2026<\/p>\n<\/td>\n<\/tr>\n
\n
‘006’<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
1190902.75<\/p>\n<\/td>\n
\n
\uf0ac\uf020region total<\/p>\n<\/td>\n<\/tr>\n
\n
\u2026<\/p>\n<\/td>\n
\n
\u2026<\/p>\n<\/td>\n
\n
…<\/p>\n<\/td>\n
 <\/td>\n<\/tr>\n
\n
NULL<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
3426563.75<\/p>\n<\/td>\n
\n
\uf0ac\uf020grand total<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The general rule in SQL is not to do data formatting in the database tier. You pass the results of the database layer to a presentation layer and that layer adds the colors, labels, does any weird filters and calculations, etc. The purpose of the database tier is just raw data. However, having said that you can write something like this:<\/p>\n
SELECT CASE WHEN GROUPING(region_nbr) =1\r\n                           THEN \u2018Region Total\u2019 ELSE region_nbr END,\r\n                CASE WHEN GROUPING(city_name) = 1\r\n                            THEN \u2018City Total\u2019 ELSE \u2018City Name\u2019 END, \r\n               SUM(sale_amt) AS sales_tot\r\n FROM Regional_Sales\r\nGROUP BY ROLLUP (region_nbr, city_name)\r\nORDER BY region_nbr, city_name; <\/pre>\n
Again, you are doing something that is not recommended.<\/p>\n
Avoiding NULLs<\/h2>\n
We debated this in the early days of ANSI X3H2. Some of the early products use the regular equality for their groupings, so each NULL became its own group. This did not work out so well. The example we had in the committee was a table of traffic tickets issued in California. Quite logically, the database designer used NULL for missing auto tags; the words none<\/em>, nothing<\/em>, missing<\/em>, etc., (in multiple languages!) had been used on prestige tags, and the way the law was written, they were perfectly legal. There was no special checkbox on the traffic tickets for a missing tag; it had to go in the space for the tag number. The huge number of missing tags made reporting impossible when they each became one row in the summary reports.<\/p>\n
As a default, you need to assume that all your columns will be NOT NULL, then go back and decide exactly what a NULL would mean for each particular column. If your column is on a nominal or categorical scale (see this article<\/a>), you can create encodings for the missing values. For example, the ISO 5218 sex codes are (\u20180\u2019 = unknown, \u20181\u2019 = male, \u20182\u2019 = female, \u20189\u2019 = not applicable or lawful person). A lawful person<\/em> includes things like corporations, governments, and so forth. If a column is a temporal data type, then NULL is often used as a symbol for eternity<\/em> when marking the open end of (start_timestamp, end_timestamp) intervals that have not closed yet.<\/p>\n
Using zero for numeric data elements may or may not work as a missing value token. Blanks or empty strings may or may not work as a missing value for text. Neither of these options have any built-in special characteristics that NULLs have in SQL.<\/p>\n
Conclusion<\/h2>\n
The simple fact is that in SQL, you really can\u2019t escape NULLs. But think of them the way you would think about any other data design decision. Is it necessary? Is it sufficient to express the nature of the model? Is it easily understood by someone who is going to have to maintain this after you\u2019re gone?<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Whether or not to have NULLable columns in a table can be a religious debate, and how missing data is represented should be carefully considered during database design. In this article, Joe Celko considers the ways that SQL Server handles NULLs in several situations.  …<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[5134],"coauthors":[6781],"class_list":["post-82836","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82836"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82836\/revisions"}],"predecessor-version":[{"id":84736,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82836\/revisions\/84736"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82836"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}