Re: Strange SQL Results

From: Phil Bradley <pbradley_at_pacific.net.sg>
Date: Tue, 11 May 1999 20:07:18 +0800
Message-ID: <37381D76.F588C27F@pacific.net.sg>

Both Remco and Sybrand are wrong!

Tri-value logic evaluates to either true, false or unknown.

For example, if a=2, b=3 and c=null then

a > b and b > c : false
a > b or b > c : unknown
a < b or b < c : true
not ( a = c ) : unknown

note, that null = null evaluates to unknown. If 'WHERE clauses treat NULL conditions as FALSE', then null = null would evaluate to true

Remco is correct about concatenations including null. It as if the null expression were not in the concatenation.

Phil Bradley

Remco Blaakmeer wrote:

> In article <923733144.17217.0.muttley.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> writes:
> > Hi Phillippa,
> >
> > This is because SQL, at least the SQL in Oracle has three valued logic. Any
> > test returns three possible values: true, false or null. I don't know better
> > than that any NULL overrides ANY results.
> > Eg if a concatenate in a string three columns and ANY of these three columns
> > is NULL the result will be NULL.
> > The same applies probably here. As columns can be NULL, if any of those
> > columns is NULL, the record will not be considered.
>
> No.
>
> 'WHERE' clauses treat NULL conditions as FALSE:
>
> SQL> select 1
> 2 from dual
> 3 where null = null
> 4 or 1 = null
> 5 or null = 1
> 6 or 1 = 1
> 7 /
>
> 1
> ----------
> 1
>
> 'null' means 'unknown', so 'null or true' equals true and 'null and
> false' equals false.
>
> If you concatenate character columns and any of them are NULL, they
> are treated as empty:
>
> SQL> select 'x' || null || 'y'
> 2 from dual
> 3 /
>
> 'X
> --
> xy
>
> Remco
> --
> rd1936: 8:55pm up 8 days, 4:51, 7 users, load average: 0.29, 0.21, 0.45

-
-
-

-

-
-

-
-
-

-

-
-

- Received on Tue May 11 1999 - 07:07:18 CDT