COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
If you have rows with variable strings of space characters that you want included in the count, use:
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.
COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
If you have rows with variable strings of space characters that you want included in the count, use:
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.
A NULL column is not countable, however a row that has a NULL column is. So, this should do what you're looking for:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL OR LENGTH(TRIM (COL_NAME)) = 0
Note, that there are non-printing characters that this will not address. For example U+00A0 is the non-breaking space character and a line containing that will visually appear empty, but will not be found by the tests above.
Videos
Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.
I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!
I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.
There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences:
You set a varchar2 to an empty string like this:
Update mytable set varchar_col = '';
the following leads to the same result
Update mytable set varchar_col = NULL;
But to select the columns where the value is empty or NULL, you have to use
select * from mytable where varchar_col is NULL;
Using
select * from mytable where varchar_col = '';
is syntactically correct, but it never returns a row.
On the other side, when concatenating strings in Oracle. NULL varchars are treated as empty strings.
select NULL || 'abc' from DUAL;
yields abc. Other DBMS would return NULL in these cases.
When you want to express explicitly, that a value is assigned, you have to use something like ' '.
And you have to worry whether trimming not empty results in NULL
select case when ltrim(' ') is null then 'null' else 'not null' end from dual
It does.
Now looking at DBMS where '' is not identical to NULL (e.g. SQL-Server)
Working with '' is generally easier and in most case there is no practical need to distinguish between both. One of the exceptions I know, is when your column represents some setting and you have not empty defaults for them. When you can distinguish between '' and NULL you are able to express that your setting is empty and avoid that the default applies.
Select *
From Table
Where (col is null or col = '')
Or
Select *
From Table
Where IsNull(col, '') = ''
If you need it in SELECT section can use like this.
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
You can replace the null with your substitution value.
This is because Oracle internally changes empty string to NULL values. Oracle simply won't let insert an empty string.
On the other hand, SQL Server would let you do what you are trying to achieve.
There are 2 workarounds here:
- Use another column that states whether the 'description' field is valid or not
- Use some dummy value for the 'description' field where you want it to store empty string. (i.e. set the field to be 'stackoverflowrocks' assuming your real data will never encounter such a description value)
Both are, of course, stupid workarounds :)
In oracle an empty varchar2 and null are treated the same, and your observations show that.
when you write:
select * from table where a = '';
its the same as writing
select * from table where a = null;
and not a is null
which will never equate to true, so never return a row. same on the insert, a NOT NULL means you cant insert a null or an empty string (which is treated as a null)
I would say that NULL is the correct choice for "no email address". There are many "invalid" email addresses, and "" (empty string) is just one. For example "foo" is not a valid email address, "a@b@c" is not valid and so on. So just because "" is not a valid email address is no reason to use it as the "no email address" value.
I think you're right in saying that "" is not the correct way to say "I don't have a value for this column". "" is a value.
An example of where "" might be a valid value, separate to NULL could be a person's middle name. Not every one has a middle name, so you need to differentiate between "no middle name" ("" - empty string) and "I don't know if this person has a middle name or not" (NULL). There's probably many other examples where an empty string is still a valid value for a column.
While agreeing with the above comments, I would add this argument as a primary motivation:
- It is obvious to any programmer looking at a database that a field marked NULL is an Optional field. (i.e. the record doesn't require data for that column)
- If you mark a field NOT NULL, any programmer should intuitively assume that it is a Required field.
- In a field that allows nulls, programmers should expect to see nulls rather than empty strings.
For the sake of Self-Documenting Intuitive Coding, use NULL instead of empty strings.