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.

Null means nothing. Its just a literal. Null is the value of reference variable. But empty string is blank.It gives the length=0. Empty string is a blank value,means the string does not have any thing.

For java 8 you can use Optional approach:

Optional.ofNullable(gearBox).orElse("");
Optional.ofNullable(id).orElse("");

Readability. Adding an empty string to an "unknown" object as a form to force it to be converted into a string is not very clear as to what your intention really is.

Hi Varsha, From my experience, Appian will always write a blank value instead of null of data type Text. The workarounds I have used in the past are: Following the writeToDataStoreEntity node, use a Query Database node with a statement such as UPDATE table SET col1 = NULL WHERE col1 = '' If this particular value is always blank/null, you can exclude it from your CDT so that Appian does not replace is with a blank value. I realize these solutions are less than ideal, and I do wish Appian would treat text fields the same as other data types and retain a NULL value in the database. I am curious if anyone else has come up with a better solution. Brad

Null is you don't know where something goes. Empty is you know where it goes but when you look there isn't anything there. The analogy being a null string has not been allocated memory to store it. While an empty string has been allocated memory but is not using it. I don't think the database connection is particularly important. its more of an implementation detail. A database may treat an empty string like a null string to save memory overhead at the cost of having to allocate a string object at runtime. Conversely the object could be constructed when the table row is initialized and save on runtime costs of initializing that field.

Oracle treats '' and NULL the same. When inserting '', there is no conversion of '' to NULL, merely an interpretation of '' as NULL in the same way that the word NULL is interpreted as NULL or rtrim('a','a') is interpreted as NULL.

Here is a demonstration using the following table and insert:

drop table t1;
create table t1 (c1 varchar2(10));
insert into t1 (c1) values ('');

The insert above inserted a NULL value for c1. You can select that row as follows:

SELECT c1 FROM t1;

When you add a WHERE clause to compare equality and one of the values being compared is NULL, the result will always be unknown. Unknown will evaluate to false except that further operations on an unknown value produce unknown values. All of the following return no rows because the WHERE clauses contain conditions that will never be true regardless of the data.

SELECT c1 FROM t1 WHERE c1 = '';
SELECT c1 FROM t1 WHERE c1 = NULL;
SELECT c1 FROM t1 WHERE '' = '';
SELECT c1 FROM t1 WHERE NULL = NULL;

Oracle provides a special syntax to retrieve rows with a particular column having null values -- IS NULL.

SELECT c1 FROM t1 WHERE c1 IS NULL;

There are a few conditions in which oracle compares NULLS treating them as equal to other NULL values such as in DECODE statements and in compound keys.

More information can be found in the SQL Language Reference.

I'm assuming you're using Java. If you leave something as null, then you have to make sure you account for NullPointerExceptions. IF it's an empty String, it's still an Object and can be referenced without causing a NPE.