NULL vs empty string

Question

What is the difference between the queries below and how it works?

SELECT * FROM some_table WHERE col IS NOT NULL 

&

 SELECT * FROM some_table WHERE col <> '' 

Regards, Mubarak

Answer 1

NULL is a special data type, it means the absence of a value.

An empty string, on the other hand, means a string or value that is empty.

Both variants.

For example, if the table has a name field, and by default it is set to NULL . If no value is specified for it, it will be NULL , but if you specify a real name or an empty string, it will not be NULL , then it will contain an empty string.

Answer 2

NULL is the absence of a value and usually indicates something meaningful, such as unknown or not defined (yet). For example, if I start a project today, StartDate is 2012-02-25 . If I don't know how long the project will take, what should EndDate be like? Maybe I know what ProjectedEndDate might be, but I would set EndDate to NULL and update it when the project is complete.

'' - a string with zero length (or "empty"). Technically, this is a lack of data, as this may make sense. For example, if I don’t have a middle name, depending on your data model, '' may make more sense than NULL , since the latter implies obscurity, but '' may mean that it is known that I do not have one. NULL can be used in the same way, but then it is difficult to decipher, it is not known whether it exists or it is known that it does not exist. Many standards have special meanings for things where this may not be known - for example, Gender I count 9 different character codes, so if M or F not specified, we always know exactly why (unknown, not specified, transgender, etc. .). Also think about where HeartRate NULL - is it because there was no impulse or because we have not yet received it?

This is not the same thing, but, unfortunately, many people treat them the same way. If your column allows NULL , it means that you know in advance that sometimes you may not know this information. If you do not view them as one and the same, your requests will be different. For example, if col does not allow NULL , your first query will always return all the results in the table, since none of them can be NULL . However, NOT NULL still allows you to enter an empty string, unless you also set a control limit to prevent zero-length strings.

The resolution for the same column is usually a bit confusing for someone trying to understand the data model, although I believe that in most cases the NOT NULL does not go with the LEN(col)>0 constraint. The problem, if both are allowed, is that it is difficult to understand what this means if the NULL column or the column is “empty” - they may mean the same thing, but they may not be - and this will vary from store to store .

Another key point is that NULL is evaluated as unknown compared to anything (at least by default in SQL Server * ), which in turn is evaluated as false. As an example, these queries return 0 :

 DECLARE @x TABLE(i INT); INSERT @x VALUES(NULL); SELECT COUNT(*) FROM @x WHERE i = 1; SELECT COUNT(*) FROM @x WHERE i <> 1; SELECT COUNT(*) FROM @x WHERE i <= 3; SELECT COUNT(*) FROM @x WHERE i > 3; SELECT COUNT(*) FROM @x WHERE i IN (1,2,3); SELECT COUNT(*) FROM @x WHERE i NOT IN (1,2,3); 

Since comparisons in the where clause are always evaluated as unknown, they always return to false, so no row ever matches the criteria, and all values ​​are returned as 0 .

Also, the answers to this question on dba.stackexchange may be helpful:

https://dba.stackexchange.com/questions/5222/why-shouldnt-we-allow-nulls

* You can change this using SET ANSI_NULLS OFF - however, this is not recommended, because it provides non-standard behavior, or because this function was deprecated with SQL Server 2005 and will become no-op in a future version of SQL Server . But you can play with the query above and see that NOT IN behaves differently with SET ANSI_NULLS OFF .

Answer 3

NULL means that there is no value, but '' means that it is a value, but only an empty row, so the first query means querying all rows whose column value is missing, the second means selecting those rows that are not equal to the empty row

Update

For more information, I suggest you read this article:

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/05/null-ndash-the-database-rsquo-s-black-hole.aspx

Answer 4

Select * from table where col IS NOT NULL will return results excluded from Select * from table where col <> '' , because an empty row is also NOT NULL.

Answer 5

http://data.stackexchange.com/stackoverflow/query/62491/http-stackoverflow-com-questions-9444638-null-vs-empty-in-sql-server

 SET NOCOUNT ON; DECLARE @tbl AS TABLE (value varchar(50) NULL, description varchar(50) NOT NULL); INSERT INTO @tbl VALUES (NULL, 'A Null'), ('', 'Empty String'), ('Some Text', 'A non-empty string'); SELECT * FROM @tbl; SELECT * FROM @tbl WHERE value IS NOT NULL; SELECT * FROM @tbl WHERE value <> ''; 

Note that on the display you cannot distinguish between NULL and `` - this is only an artifact about how the grid and text data display the data, but the data in the set is stored differently for NULL and ''.

Answer 6

As pointed out in other answers, NULL means “no value”, while an empty string '' means only that - an empty string. You can think of fields that allow NULLs as optional fields - you can ignore them, and the value for them may simply not be provided.

Imagine an application in which the respondent chooses his name (Mr, Mrs, Miss, Dr), but you do not require that he / she select any of them and leave it blank. In this case, you must put NULL in the appropriate database field.

The difference between NULL and an empty string may not be obvious, because both of them can mean "no value" if you decide. It is entirely up to you, but using NULL would be better, mainly because it is a special case for databases that are designed to handle NULL quickly and efficiently (much faster than strings). If you use it instead of an empty string, your queries will be faster and more reliable.