Why does SELECT 'foo' + NULL; returns NULL on SQL Server

Question

Why does SELECT 'foo' + NULL; returns NULL on SQL Server? I would expect it to return foo.

Update: The SQL query is generated by Entity Framework Core from C# and I expected the null handling behavior to be translated.

Your expectation is wrong. Almost any operation with `NULL` returns `NULL`, including string concatenation. — Gordon Linoff, Dec 02 '18 at 23:39
`NULL` and an empty string (`’’`) are two different things. — tarheel, Dec 02 '18 at 23:40
Technically, if you wanted to do this, you could set `CONCAT_NULL_YIELDS_NULL` off beforehand. But it's like that for a reason, so... (Also this feature will be removed at some point so it's probably wise to not use it.) — ZLK, Dec 02 '18 at 23:45
@LukStorms the code is generated by the EF Core ORM, from C# — mobal, Dec 02 '18 at 23:55

GMB · Answer 1 · 2018-12-03T00:34:11.920

3

Well, that's the whole concept of NULL. It indicates a value that is missing in the database, hence making most operations inapplicable :

Any arithmetic operation on NULL (addition, substraction, multiplication, division on both ends) returns NULL.
String concatenation with NULL returns NULL (on most RDBMS, but not Oracle, where NULL is treated as the empty string, hence a query like select NULL || 'A' from dual returns A)
Comparison with NULL (equal, different, like) never match. You usually need to use specific functions such as ISNULL to test if a value is NULL

For more information please have a look at this Wikipedia page

edited Dec 03 '18 at 00:34

answered Dec 02 '18 at 23:46

GMB

216,147
25
84
135

i completely understand the reason behind the described null handling in the case of logical and numeric values, most programming languages treat null the same way, but i was surprised with the `string+` operator specifically. – mobal Dec 03 '18 at 00:02
Yes @mobal, I agree that this is tricky. Pls note that Oracle RDBMS works differently and happily treats `NULL` as the empty string when used in string concatenation context. Updated my answer with this information. – GMB Dec 03 '18 at 00:26

score 3 · Accepted Answer · answered Dec 02 '18 at 23:53

There are two ways you can handle the concatenation with NULL by setting CONCAT_NULL_YIELDS_NULL as ON or OFF.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';  
GO  
-- SET CONCAT_NULL_YIELDS_NULL ON and testing.  
SET CONCAT_NULL_YIELDS_NULL ON;  
GO  
SELECT 'abc' + NULL ;  
GO  


--SET CONCAT_NULL_YIELDS_NULL OFF and testing.  
SET CONCAT_NULL_YIELDS_NULL OFF;  
GO  
SELECT 'abc' + NULL;   
GO

When the CONCAT_NULL_YIELDS_NULL is on, it will yield a NULL result, and vice versa.

When this setting is not specified, then the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

To view the current setting for this setting, run the following query

DECLARE @CONCAT_NULL_YIELDS_NULL VARCHAR(3) = 'OFF';  
IF ( (4096 & @@OPTIONS) = 4096 ) SET @CONCAT_NULL_YIELDS_NULL = 'ON';  
SELECT @CONCAT_NULL_YIELDS_NULL AS CONCAT_NULL_YIELDS_NULL;

["*In a future version of SQL Server `CONCAT_NULL_YIELDS_NULL` will always be `ON` and any applications that explicitly set the option to `OFF` will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.*"](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-2017) — GSerg, Dec 03 '18 at 00:10

score 1 · Answer 3 · answered Dec 02 '18 at 23:44

NULL doesn't mean "nothing" or "empty". That's a common misconception. NULL really means "I don't know". It might be empty... but it might be something else; you just don't know what to put there yet.

When you you combine "foo" with "I don't know", the answer is still "I don't know". Pretty much all databases work the same way; it's what the SQL spec requires.

Why does SELECT 'foo' + NULL; returns NULL on SQL Server

3 Answers3