Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]
Date: Fri, 10 Jun 2005 09:01:08 GMT
"Jon Heggland" <heggland_at_idi.ntnu.no> wrote in message
> In article <AC5qe.11727$F7.5746_at_news-server.bigpond.net.au>,
> hobbit_at_southern_seaweed.com.op says...
>> >> And x + NULL does not evaluate to NULL but x.
>> > I should have mentioned that I am talking about the SQL2 standard (or
>> > SQL/92 or SQL:1992 or SQL-92 or whatever it is called). I am not
>> > familiar with MS TSQL.
>> It appears to be an ISO/ANSI SQL standard. SQL-99:
> What does NULL + NULL evaluate to? NULL or 0?
It's dependent upon the resident SET command and the data type of the operands. Here is the MS SQL ref, but I have no idea at the moment whether this is representative of how other SQL's treat the management of the null.
"When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.
If not specified, the setting of the concat null yields null database option applies."
> How about the sum of an attribute of an empty relation? Or of a column
> with just NULLs?
Dependent upon the SET command.
> How about NULL - NULL? And NULL = NULL? Is x = y and x - y = 0
> equivalent (x and y being integer range variables)?
Again it's all dependent on the SET command.
Yes, if you have SET CONCAT_NULL_YIELDS_NULL OFF, then you'll get 0 if x is null, but x if it is not. OTOH if you have SET CONCAT_NULL_YIELDS_NULL ON then if x is null you'll get null if x is null, but x if it is not.
-- Pete Brown Falls Creek OZ www.mountainman.com.auReceived on Fri Jun 10 2005 - 11:01:08 CEST