What's the difference between NULL and ""

Using 7.3 on AIX I have a table structure similar to this: create table fred ( rsn serial not null , last_name char(20) ); What is the difference between the following two INSERT statements: INSERT INTO FRED (rsn, last_name) VALUES ( 0, NULL ); INSERT INTO FRED (rsn, last_name) VALUES ( 0, "" ); If I do the following: SELECT * FROM fred WHERE last_name is NULL It only shows me one record (the first) and SELECT * FROM fred WHERE last_name = "" only shows me the second record. Am I mistaken? I always assumed that a quoted string with no literal (ie. "") was interpreted the same as a NULL. Do I have some onscure 'feature' set?

FProse, FProse wrote: > Using 7.3 on AIX I have a table structure similar to this: > > create table fred > ( > rsn serial not null , > last_name char(20) > );> > What is the difference between the following two INSERT statements: > > INSERT INTO FRED (rsn, last_name) VALUES ( 0, NULL ); > INSERT INTO FRED (rsn, last_name) VALUES ( 0, "" );> > If I do the following: > > SELECT * FROM fred WHERE last_name is NULL> > It only shows me one record (the first) and That seems correct to me. > > SELECT * FROM fred WHERE last_name = ""> > only shows me the second record. That also seems correct to me. > Am I mistaken? I always assumed that a quoted string with no literal > (ie. "") was interpreted the same as a NULL. Do I have some onscure > 'feature' set? I say you're mistaken. NULL indicates the the absence of data. One thing you should try is this... UNLOAD TO fred.txt SELECT * FROM fred I'm not sure what you would see, but likely in the output fred.txt you would not be able to distinguish between "NULL" and "". - Randy Galbraith Spammers please note: I'm very hostile toward spammers, I go to great lengths to have accounts shutdown, etc. So... don't send me any UCE/Spam.

Randy Galbraith wrote: > FProse wrote: > > > Using 7.3 on AIX I have a table structure similar to this: > > > > create table fred > > ( > > rsn serial not null , > > last_name char(20) > > );> > > > What is the difference between the following two INSERT statements: > > > > INSERT INTO FRED (rsn, last_name) VALUES ( 0, NULL ); > > INSERT INTO FRED (rsn, last_name) VALUES ( 0, "" );> > > > If I do the following: > > > > SELECT * FROM fred WHERE last_name is NULL> > > > It only shows me one record (the first) and > > SELECT * FROM fred WHERE last_name = ""> > > > only shows me the second record. Yup, that's the correct behavior all right. > > Am I mistaken? I always assumed that a quoted string with no literal > > (ie. "") was interpreted the same as a NULL. Do I have some onscure > > 'feature' set? > > I say you're mistaken. NULL indicates the the absence of data. One > thing you should try is this... > > UNLOAD TO fred.txt > SELECT * FROM fred> > I'm not sure what you would see, but likely in the output fred.txt you > would not be able to distinguish between "NULL" and "". Not true. Since last_name was defined as CHAR, you would see 1|| 2| | In a CHAR(20) field, "" is stored as 20 spaces, all but one of which is stripped off when being unloaded, just for this distinction. Don't try this experiment with VARCHAR, however. June -- june_t@hotmail.com Back in Palo Alto, living on Kit Kat bars and chocolate chip cookies Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.

Well... 1. "" is different from NULL. 2. You cannot insert a serial value, try 'insert into fred (last_name) values ('smith)' SC > Using 7.3 on AIX I have a table structure similar to this: > > create table fred > ( > rsn serial not null , > last_name char(20) > );> > What is the difference between the following two INSERT statements: > > INSERT INTO FRED (rsn, last_name) VALUES ( 0, NULL ); > INSERT INTO FRED (rsn, last_name) VALUES ( 0, "" );> > If I do the following: > > SELECT * FROM fred WHERE last_name is NULL> > It only shows me one record (the first) and > > SELECT * FROM fred WHERE last_name = ""> > only shows me the second record. > > Am I mistaken? I always assumed that a quoted string with no literal > (ie. "") was interpreted the same as a NULL. Do I have some onscure > 'feature' set?

"Stephen F. Cawley" wrote: > 1. "" is different from NULL. Correct. > 2. You cannot insert a serial value, try 'insert into fred (last_name) > values ('smith)' That is not correct. You can insert any valid INTEGER into a SERIAL column. Your example will work and will insert the next available serial value into the SERIAL column, but only a unique index will prevent you from inserting any INTEGER value. If you insert "0", the next serial value will be picked. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

+---- fprose@rocketmail (FProse) wrote (Wed, 7 Apr 1999 08:29:45 -0700 ): |Am I mistaken? I always assumed that a quoted string with no literal |(ie. "") was interpreted the same as a NULL. Do I have some onscure |'feature' set? The use of '' to mean NULL is an Oracle perversion. The use of double-quotes is an Informix perversion. The pityful bastards don't even *try* to implement the full SQL standard before they extend their proprietary non-standard implementations. Yuck. The 1987 SQL standard did not allow zero length strings. The 1992 SQL standard was extended to allow it. IMHO, an RDB tool or application which uses '' for NULL, or uses double-quotes instead of apostrophes, is not SQL compliant. /pesky/