Re: Strange SQL Results

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 Mon May 10 1999 - 14:47:27 CDT