Re: SUMMARY: SQL question

Sorry for the previous bogus reply...

|>
|>|>Thanks for all the replies I received.
|>|>
|>|>The reason I asked the question was that when I use SQL with RDB on VMS and
|>|>Ingres on Unix, the following statements always give the same results:
|>|>
|>|>SQL> select count(*) from table_one where field_one = '';
|>|>
|>|>SQL> select count(*) from table_one where field_one = ' ';
|>|>
|>|>I guess this means that RDB and Ingres are incorrect!?! :-)
|>|>
|>|>Thanks again,
|>|>
|>|>Dave
|>|>
|>|>P.S. Does anyone know of an archive site where I can find a copy of
|>|> the latest SQL standard??
|>|>
|>
Actually what your presented is interesting and there is no difference between the following two queries:

select employee_id from employees where last_name = ''; select employee_id from employees where last_name = ' ';

The SQL standard is fairly straight forward in this. I will quote from the SQL92 standard:

1. Null Value (NULL): A special value, or mark, that is used to indicate the absence of any data value.
2. There is no <literal> for a null value, although the keyword NULL is used in some places to indicate that a null value is desired.

Now how does this end up meaning

select employee_id from employees where last_name = ''; select employee_id from employees where last_name = ' ';

are the same.

Here is an example:

Example 1:

Assume employees table will all fixed character columns.

insert into employees (employee_id,last_name,first_name)

        values ('11111','',' ');

The columns employee_id,last_name,first_name are specified in the insert statement and are all assigned literals. Thus any other columns in employees are NULL values since they are not specified in the insert statement.

When assigning values to each of these columns the SQL92 standard is clear that:

1. Let T and V be a TARGET and VALUE speicified in an application...
2. If V is not the null value (which it isn't since a literal is specified)...
3. If the datatype of T is fixed-length character string with length L, and the length in characters M of V is smaller than L, then the first M characters of T are set to V, and the last L-M characters of T are set to spaces.

So in this case since NULL or the ommission of tha column reference is the only way to indicate null and "literal" is used. Then you end up storing a fully padded column of spaces.

For people that think this isn't intuitive and expressed that the = ' ' is the same as doing an evaluation of the column value to a hex 20....think about why the following queries for a last_name column would all return the same results:

select employee_id from employees where last_name = ' ';
select employee_id from employees where last_name = '  ';
select employee_id from employees where last_name = '   ';
select employee_id from employees where last_name = '    ';
select employee_id from employees where last_name = '     ';