Hi Jonathan,

> >>>You're answering your own question: '' (empty string) is NOT NULL.
> >>>NULL is NULL (empty). An empty string is a value. NULL is a state,
> >>>so is "NOT NULL".
> >>>
> >>>
> >>>
> >>IMHO, this unique feature could nearly be considered a bug...
> >>
> >>
> >
> >A bug? Why? NULL is NOT an empty string. An empty string
> >is a value, NULL is a state.
> >
> >A column can have two states:
> >
> >NULL
> >NOT NULL
> >
> >A string column can have all sorts of values, ranging from an
> >empty string to pretty much anything you can put into it.
> >
> Yes, yes, I know. I understand this, but you must admit that in
> practice, an empty string, is really nothing. 0 is a value, and
> therefore it makes sense that there would be a distinction between 0 and
> null. However, '' can hardly be considered a value. What's the
> difference between not putting anything in a column, and putting nothing
> in it? :-)
>
> The main thing is that it leads to much confusion, and that I can't
> think of any situation where this subtle distinction would be useful. In
> fact, if you look at TField.AsString (in Delphi), it returns '' if the
> field is null. That's because Delphi doesn't have this subtle
> distinction (of course). In practice, why make this distinction?
>
> 0 is a value, and visually, 0 is represented by a visible character.
> There's a difference between saying that a certain price is 0 for
> instance, and saying that we don't know the price. But what difference
> could there ever be between '' and null, in practice?

I can easily argue that 0 isn't a value in some cases.

NULL doesn't mean empty (''), it means "unknown" or "not applicable".
That's the difference. If a string value is applicable, it might be ''
instead of something else.

And yes, there are also plenty of situations where NULL or '' can
be interchanged. But just not in SQL.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com