Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]

From: mountain man <hobbit_at_southern_seaweed.com.op>
Date: Fri, 10 Jun 2005 09:01:08 GMT
Message-ID: <oFcqe.12059$F7.3016_at_news-server.bigpond.net.au>

"Jon Heggland" <heggland_at_idi.ntnu.no> wrote in message news:MPG.1d1362b946561e4e98968f_at_news.ntnu.no...
> 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.

===========[MS SQL]====================

"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."

===========[/MS SQL]====================

> 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.

> If you evaluate x + 0, will you get 0 if x is NULL, but x if it is not?

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.au

Received on Fri Jun 10 2005 - 11:01:08 CEST