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!
Answer from Larry Coleman on Stack Exchangefeature comparison - When to use NULL and when to use an empty string? - Database Administrators Stack Exchange
How to check for Is not Null And Is not Empty string in SQL server? - Stack Overflow
How to handle a value which is not null but also not an empty string?
"NOT NULL" property in SQL Server Express 2019
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.
If you only want to match "" as an empty string
WHERE DATALENGTH(COLUMN) > 0
If you want to count any string consisting entirely of spaces as empty
WHERE COLUMN <> ''
Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.
CREATE TABLE T
(
C VARCHAR(10)
);
INSERT INTO T
VALUES ('A'),
(''),
(' '),
(NULL);
SELECT *
FROM T
WHERE C <> ''
Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.
SQL Fiddle
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).
I have a table which contains some values which are not null. I thought it might be an empty sting but it was not an empty string either.
I copied that particular field from the table and tried an update statement to convert it into null but it didn't worked either . Kindly suggest something
Lets say i have a Excel sheet , col : name and ID . some rows cells in "name" are empty in datasheet.
now sql pulls this data, will the empty cells be filled with a null value by default?
or is null a unique type of character that is only used to overwrite existing values , so when system reads it it thinks its empty?
Consider checking documentation:
NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.
If you consider that the value of istrue is unknown in the NULL case then it might or might not equal 1.
The expression istrue != 1 then evaluates to unknown
SQL only returns rows where the WHERE clause evaluates to true.
If you are on SQL Server 2022+ you can use
WHERE istrue IS DISTINCT FROM 1
To give the inequality semantics that you want (Fiddle).