Re: Weird "select count(*)..."

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/09
Message-ID: <339c2d3b.40693494@newshost>#1/1

You must have NULLS in the table. Consider:

SQL> select * from dual;  

D
-
X  

SQL> select * from dual where null = null;  

no rows selected  

SQL> select * from dual
  2 where null = null
  3 OR null <> null;  

no rows selected  

Null is NEVER equal to NULL
Null is NEVER Not equal to NULL

You need to say something like:

where ( a = a OR a is null )
  and ( b = b OR b is null )
....

On 9 Jun 1997 13:03:35 GMT, "Eric L'HUILLIER" <elhuilli_at_ifremer.fr> wrote:

>Hi Netters,
>
>I tried the following queries :
>
>SQL> select count(*) from plan_comptable;
>
> COUNT(*)
>---------
> 1784
>

 [snip]
>
>SQL> select count(*) from plan_comptable
> 2 where pcg_numcptgen = pcg_numcptgen
> 3 and pcg_libcptgen = pcg_libcptgen
> 4 and pcg_typsolde = pcg_typsolde
> 5 and pcg_duramort = pcg_duramort
> 6 and pcg_detail = pcg_detail
> 7 and pcg_numlig = pcg_numlig;
>
> COUNT(*)
>---------
> 0
>
>I think I should had the same result on both queries. Don't you think so ?
>If not why.
>

 [snip]
>
>eric.l.huillier_at_ifremer.fr
>
>IFREMER Brest (FRANCE)
>
>Tel : 02.98.22.47.40 (From France)
>Tel : +(33) 2.98.22.47.40 (From rest of the world)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 09 1997 - 00:00:00 CDT