RE: Riddle me this.....

Date: Thu, 9 Apr 2009 15:44:24 -0400
Message-ID: <>

This returns a count of 0.

P Consider the environment. Please don't print this e-mail unless you really need to.

From: Jared Still [] Sent: Thursday, April 09, 2009 3:43 PM
Subject: Re: Riddle me this.....

On Thu, Apr 9, 2009 at 11:47 AM, SHEEHAN, JEREMY <<>> wrote: Ok. Here's an interesting question that some developers threw at me. I don't see anything wrong with the statement but it just does not work....

This statement does not work.....

select * from pjm_nm_sttl_component

   where sttl_id in -- this statement has the 'in'  (select sttl_id_iso from isolink_pjm_sttl_estim_w_iso

    where sttl_id = '131545258'

try this

select count(*)
from isolink_pjm_sttl_estim_w_iso
where sttl_id = '131545258'
and sttl_id_iso is null
IIRC the occurrence of NULL in the values returned can lead to 'surprising' results when used with an IN list.

I'm fairly certain Jonathan Lewis and/or Tom Kyte, and no doubt many others have written about this.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Thu Apr 09 2009 - 14:44:24 CDT

Original text of this message