Re: [sqlite] SELECT on empty fields ??
There are good reasons to have "NULL" values - though they can make the live a bit harder. Use them - even Microsoft has found out, that they made a big mistake in .NET 1.x and corrected them in 2.0 by introducing nullable types. Darren Duncan schrieb: You can save your self a lot of grief by declaring all of your fields to be NOT NULL and default the text fields to the empty string, ''. Use '' rather than NULL when you don't have a name. Then you can simply say "where foo=''". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT on empty fields ??
That is why everything in the world is not painted grey. There are those of us who find null values distinct and meaningful and those who don't. I personally prefer null (Unknown, etc.) values versus contrived values which in effect mean I don't like dealing with nulls so here's a blank string, or is that an empty string, missing value? Or worse, zero vs. unknown? Damn! there goes the AVG(), Count(), and etc. function accuracy out the window! Oh well, it sure is easy to code! Fred > -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Monday, November 27, 2006 6:51 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SELECT on empty fields ?? > > > At 5:36 PM -0600 11/27/06, Jay Sprenkle wrote: > >On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote: > >>I'd like to strongly second this. Avoid NULL columns, even > at apparent cost. > >>Having a valid default value is always better. If a design > appears to > >>require NULL values, then the design is likely critically flawed. > > > >Using NULLS is NOT a critical design flaw. > >NULL means something specific and if you use it correctly it > works perfectly. > >NULL indicates when nothing has been entered into a field. > >Not entering anything, and entering spaces or a default > value, are different. > >If you need that information then it's very useful. If you don't then > >don't use it > >by assigning default values. > > Perhaps an intention behind the existence of NULLs was a useful idea, > but in practice, they are a big mess in SQL. > > The NULL is used in SQL for a multiplicity of unrelated meanings, > some of which are: value is unknown, no value is applicable here, > value is at its default / has yet to be assigned to, value can not be > determined, result of that operation is invalid. > > In fact, I read somewhere that there are a good 12 distint meanings > attached to NULLs, so we don't have 3-valued-logic, its > 14-valued-logic. > > But regardless, if you are given a NULL, how do you know what > it means? > > Moreover, SQL is inconsistent with itself in its treatment of NULLs, > in some situations treating 2 nulls as being distinct, and in other > situations treating them as non-distinct. > > So NULLs can be helpful to you if you are very careful, but often > they are more trouble than they are worth, and wherever possible, one > should use some other way to express the meaning of what they were > using NULLs for. > > -- Darren Duncan > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
At 5:36 PM -0600 11/27/06, Jay Sprenkle wrote: On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote: I'd like to strongly second this. Avoid NULL columns, even at apparent cost. Having a valid default value is always better. If a design appears to require NULL values, then the design is likely critically flawed. Using NULLS is NOT a critical design flaw. NULL means something specific and if you use it correctly it works perfectly. NULL indicates when nothing has been entered into a field. Not entering anything, and entering spaces or a default value, are different. If you need that information then it's very useful. If you don't then don't use it by assigning default values. Perhaps an intention behind the existence of NULLs was a useful idea, but in practice, they are a big mess in SQL. The NULL is used in SQL for a multiplicity of unrelated meanings, some of which are: value is unknown, no value is applicable here, value is at its default / has yet to be assigned to, value can not be determined, result of that operation is invalid. In fact, I read somewhere that there are a good 12 distint meanings attached to NULLs, so we don't have 3-valued-logic, its 14-valued-logic. But regardless, if you are given a NULL, how do you know what it means? Moreover, SQL is inconsistent with itself in its treatment of NULLs, in some situations treating 2 nulls as being distinct, and in other situations treating them as non-distinct. So NULLs can be helpful to you if you are very careful, but often they are more trouble than they are worth, and wherever possible, one should use some other way to express the meaning of what they were using NULLs for. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
In my experience, NULL has been used by inexperienced developers to great detriment to the stability of their projects. Please note my use of the word "likely" and the definition of the given word. In a large portion of cases there is no reason that there would be a "missing" or "unknown" value. There are cases where it could be useful, but in the vast majority of cases it causes much more work than needed (constantly checking for a NULL value etc). It triples all boolean logic for instance - true, false, and null conditions. On 11/27/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote: > I'd like to strongly second this. Avoid NULL columns, even at apparent cost. > Having a valid default value is always better. If a design appears to > require NULL values, then the design is likely critically flawed. Using NULLS is NOT a critical design flaw. NULL means something specific and if you use it correctly it works perfectly. NULL indicates when nothing has been entered into a field. Not entering anything, and entering spaces or a default value, are different. If you need that information then it's very useful. If you don't then don't use it by assigning default values. - To unsubscribe, send email to [EMAIL PROTECTED] - -- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management
Re: [sqlite] SELECT on empty fields ??
On 11/27/06, Isaac Raway <[EMAIL PROTECTED]> wrote: I'd like to strongly second this. Avoid NULL columns, even at apparent cost. Having a valid default value is always better. If a design appears to require NULL values, then the design is likely critically flawed. Using NULLS is NOT a critical design flaw. NULL means something specific and if you use it correctly it works perfectly. NULL indicates when nothing has been entered into a field. Not entering anything, and entering spaces or a default value, are different. If you need that information then it's very useful. If you don't then don't use it by assigning default values. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT on empty fields ??
> -Original Message- > From: Isaac Raway [mailto:[EMAIL PROTECTED] > Sent: Monday, November 27, 2006 7:05 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SELECT on empty fields ?? > > I'd like to strongly second this. Avoid NULL columns, even at apparent > cost. > Having a valid default value is always better. If a design appears to > require NULL values, then the design is likely critically flawed. For > something this simple, a default '' string would be much better. There's a difference between allowing NULL values and requiring NULL values. NULL has its place in the world of data. There's a difference between "Has no last name" and "Last name is unknown". A '' last name should mean "no last name" and a NULL last name should mean "last name is not known". If your design pattern allows one to fill in partial bits of information like a first name without a last name or vice versa, then NULL is a useful value. Robert - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
I'd like to strongly second this. Avoid NULL columns, even at apparent cost. Having a valid default value is always better. If a design appears to require NULL values, then the design is likely critically flawed. For something this simple, a default '' string would be much better. On 11/26/06, Darren Duncan <[EMAIL PROTECTED]> wrote: You can save your self a lot of grief by declaring all of your fields to be NOT NULL and default the text fields to the empty string, ''. Use '' rather than NULL when you don't have a name. Then you can simply say "where foo=''". -- Darren Duncan At 10:52 PM +0100 11/26/06, Daniel Schumann wrote: >Hello, > >i got a table 't' with two fields for example : > >Lastname | Name >- >Duck | Donald > | Peter > >with : > >SELECT * FROM t WHERE (Lastname='Duck') AND (Name='Donald') > >everything is all right >with : > >SELECT * FROM t WHERE (Lastname='') AND (Name='Peter') >or >SELECT * FROM t WHERE (Lastname=NULL) AND (Name='Peter') > >nothing is selected > >what do i wrong ? >thx - To unsubscribe, send email to [EMAIL PROTECTED] - -- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management
Re: [sqlite] SELECT on empty fields ??
You can save your self a lot of grief by declaring all of your fields to be NOT NULL and default the text fields to the empty string, ''. Use '' rather than NULL when you don't have a name. Then you can simply say "where foo=''". -- Darren Duncan At 10:52 PM +0100 11/26/06, Daniel Schumann wrote: Hello, i got a table 't' with two fields for example : Lastname | Name - Duck | Donald | Peter with : SELECT * FROM t WHERE (Lastname='Duck') AND (Name='Donald') everything is all right with : SELECT * FROM t WHERE (Lastname='') AND (Name='Peter') or SELECT * FROM t WHERE (Lastname=NULL) AND (Name='Peter') nothing is selected what do i wrong ? thx - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SELECT on empty fields ??
NULL is not equal to anything, including itself. ('' = NULL) is false,
(NULL = NULL) is also false. To test for NULL, use IS NULL, such as :
SELECT * FROM t WHERE (LastName = '' OR LastName IS NULL) AND (Name =
'Peter')
-Original Message-
From: Daniel Schumann [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 26, 2006 2:52 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] SELECT on empty fields ??
Hello,
i got a table 't' with two fields for example :
Lastname | Name
-
Duck | Donald
| Peter
with :
SELECT * FROM t WHERE (Lastname='Duck') AND (Name='Donald')
everything is all right
with :
SELECT * FROM t WHERE (Lastname='') AND (Name='Peter')
or
SELECT * FROM t WHERE (Lastname=NULL) AND (Name='Peter')
nothing is selected
what do i wrong ?
thx
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
