Better to store 0 or null in a column

Question

Better to store 0 or null in a column

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

Hey .Netter, I'm not sure that we helped you out here and I don't want to leave it like that.

What did you mean by better? Without that we seemed to have piled on information that might not have anything to do with your problem at all. Not that there is anything incorrect with what has been said here.

Please tell us what you meant by better and we will help you increase your understanding of that. We were all new at this at some point.

One point though. Even a sodded old yank like me understands that there is a difference between not knowing and being daft. Not knowing requires telling. It's the not asking that's daft.

ATBCharles Kincaid

Michael Meierruth SSChampion Points: 10051 More actions · Answer 2

Eric M Russell (12/17/2014)
DonlSimpson (12/17/2014)
As a side note, on some RDMS platforms, various stubbed values are treated literally as NULL. For example, Oracle treats an empty string as NULL.
What this means is that ...
'' is null : TRUE
'Smith' > '' : FALSE
'' = '' : FALSE
I discovered that the hard way when working on a project that exchanged data between SQL Server and Oracle.

Oracle does have an interesting concept of null. Thus all of these return 'ac':

select replace('abc','b','') from dual;

select replace('abc','b',null) from dual;

select 'a'||null||'c' from dual;

But this returns null:

select 1+null+3 from dual;

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

Michael Meierruth (12/18/2014)
Eric M Russell (12/17/2014)
DonlSimpson (12/17/2014)
As a side note, on some RDMS platforms, various stubbed values are treated literally as NULL. For example, Oracle treats an empty string as NULL.
What this means is that ...
'' is null : TRUE
'Smith' > '' : FALSE
'' = '' : FALSE
I discovered that the hard way when working on a project that exchanged data between SQL Server and Oracle.
Oracle does have an interesting concept of null. Thus all of these return 'ac':
select replace('abc','b','') from dual;
select replace('abc','b',null) from dual;
select 'a'||null||'c' from dual;
But this returns null:
select 1+null+3 from dual;

Regarding Oracle, one consequence is that an empty string is not equal to another empty string (because it's considered NULL). For example, if MIDDLE_NAME= '', a query WHERE MIDDLE_NAME = '' will not return a positive match.

Many times in SQL Server, we populate a varchar columns with an empty string specifically to differentiate it from NULL. That's what tripped me up when porting a database and some SQL to Oracle.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho