{"id":50,"date":"2008-01-16T21:58:03","date_gmt":"2008-01-16T19:58:03","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/01\/16\/comparisons-with-null\/"},"modified":"2008-01-16T21:58:03","modified_gmt":"2008-01-16T19:58:03","slug":"comparisons-with-null","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/01\/16\/comparisons-with-null\/","title":{"rendered":"Comparisons with NULL"},"content":{"rendered":"

Or ‘True, False or FileNotFound’ <\/em>(1)<\/sup><\/p>\n

Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when it is used in a comparison, it returns results that look surprising. Take this as a quick example<\/p>\n

DECLARE @Var1 INT
\nSET @Var1 = NULL<\/p>\n

SELECT
\nCASE WHEN @Var1 = NULL THEN ‘Var1 = Null’ ELSE ‘Var1 <> NULL’ END AS EqualityTest,
\nCASE WHEN @Var1 <> NULL THEN ‘Var1 <> Null’ ELSE ‘Var1 = NULL’ END AS InequalityTest<\/p>\n

The results look a little strange. They’re supposed to. (No, I didn’t mean IS NULL\/IS NOT NULL)<\/p>\n

SQL uses what’s know as three-state logic. A boolean expression can be true, false or NULL. If any of the values in the expression are NULL, it will return NULL. This has interesting implications for conditions in a query.<\/p>\n

Let’s take a simple table that has a value column with numbers from 1-10 and null (creation code at the end of the article)<\/p>\n

A condition WHERE Value = 5 will, as expected, only return the rows where value = 5
\nA condition WHERE Value <> 5 will return the values 1-4 and 6-10, but not NULL.
\nOnly a condition of WHERE Value IS NULL will return the null row. Anything else eliminates the null row.<\/p>\n

Why is this so important? Well, often I see code like the following:<\/p>\n

SELECT …
\nWHERE ISNULL(SomeStringColumn,”) <> ” (2)<\/sup><\/p>\n

Looks fairly simple. Returns all rows that don’t have null or empty string in the string column. Fair enough, it does work. There’s one big problem with that though. The function on the column prevents index usage (assuming there’s an index on the column). It’s also totally unnecessary.<\/p>\n

As shown above, any condition on a column (except IS NULL) will eliminate null rows. So that query could be expressed as<\/p>\n

SELECT …
\nWHERE SomeStringColumn <> ”<\/p>\n

and it will return exactly the same values, will allow effective index usage, and won’t force the CPU to do unnecessary computations.<\/p>\n

(1) What is Truth?<\/a>
\n(2) Thank to Jeff Moden<\/a> from SQLServerCentral.com<\/a> who initially brought this query and issue up.<\/p>\n

Create Table #TestingNulls (
\nValue INT
\n)
\nGO<\/code>Insert into #TestingNulls VALUES (1)
\nInsert into #TestingNulls VALUES (2)
\nInsert into #TestingNulls VALUES (3)
\nInsert into #TestingNulls VALUES (4)
\nInsert into #TestingNulls VALUES (5)
\nInsert into #TestingNulls VALUES (6)
\nInsert into #TestingNulls VALUES (7)
\nInsert into #TestingNulls VALUES (8)
\nInsert into #TestingNulls VALUES (9)
\nInsert into #TestingNulls VALUES (10)
\nInsert into #TestingNulls VALUES (NULL)
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

Or ‘True, False or FileNotFound’ (1) Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,17],"tags":[],"class_list":["post-50","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-O","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/50","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=50"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/50\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}