Thread: simple SQL query

simple SQL query

From

"Kevin Duffy"

Date:

29 October 2008, 17:45:18

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a couple of queries that are giving me headaches.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">They are actually very simple, but I do not understand why </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">I am not getting the expected results.  Maybe I need new glasses.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Please be kind.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The table definitions are below.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The table TMP_INDEX_MEMBER contains 21057 rows.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">These rows contain 3167 distinct ISINs.  ISIN is a type of unique security
identifier.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">This query </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">select * from security </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">   where securitytypekey NOT IN ( 5,27) and  ISIN IN </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  (select ISIN from tmp_index_member )</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">returns 3069 rows.  This tells me that there are 3069 ISINs</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">in the SECURITY table. ISINs that I already know about.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">update tmp_index_member set securitykey = security.securitykey</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">   from security </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">   where securitytypekey NOT IN (5,27)  and tmp_index_member.ISIN = security.ISIN</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">results in Query returned successfully: 20545 rows affected, 2169 ms execution time.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">There are now 512 row in TMP_INDEX_MEMBER that have not been updated.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">OK now the confusion begins.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I would expect the following query to return 512 rows.  It returns zero.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">select * from tmp_index_member tim</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  where tim.ISIN  NOT IN </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) )</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I want to add to SECURITY the securities that are new to me.  To do this I need the above
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">query to work.  </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Question:  does a UNIQUE constraint create an index? </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Maybe your fresh eyes will see something obvious.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">Many thanks to taking a look at this issue.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">KD</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SECURITY – contains the list my in-house security list</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE "security"</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  securitykey serial NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  securitytypekey integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  securitydesc character varying(125),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  bbcode character(25),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  ric character(15),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  sedol character(15),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  cusip character(12),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  isin character(15),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  securityissuecurriso character varying(3),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  underlyingcusip character varying(15),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  ticker character(30),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  underlyingisin character varying(15),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  expirationdate date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  strikeprice numeric(19,6),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  put_call character(1),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  multiplier integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  createdate timestamp without time zone DEFAULT now(),</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  ccy1isocode character(3),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  ccy2isocode character(3),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  contractdate date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  fwdrate numeric(15,8),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  contract character(25),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  contractsize integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  unitprice numeric(10,6),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  underlyingticker character(20),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  underlyingbloomberg character(20),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  couponrate numeric(15,8),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  maturitydate date,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  exchangekey integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  CONSTRAINT pk_security PRIMARY KEY (securitykey),</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT fk_security_securitytype FOREIGN KEY (securitytypekey)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">      REFERENCES securitytype (securitytypekey) MATCH SIMPLE</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">      ON UPDATE NO ACTION ON DELETE NO ACTION,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT unq_security_cusip UNIQUE (cusip, securitytypekey,
securityissuecurriso),</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT unq_security_isin UNIQUE (isin, securitytypekey, securityissuecurriso)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">WITHOUT OIDS;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">ALTER TABLE "security" OWNER TO postgres;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-- Index: security_bbcode</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-- DROP INDEX security_bbcode;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE INDEX security_bbcode</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  ON "security"</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  USING btree</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  (bbcode, securitytypekey);</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-- Index: "security_sectype_isoCurr"</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-- DROP INDEX "security_sectype_isoCurr";</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE INDEX "security_sectype_isoCurr"</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  ON "security"</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  USING btree</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  (securitytypekey, securityissuecurriso);</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">TMP_INDEX_MEMBER – contains the members of indexes such as S&P 500 and Russell
1000</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE tmp_index_member</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">  tmp_index_member_key serial NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  index_key integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  taskrunkey integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  isin character(15),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  cusip character(12),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  sedol character(12),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  bbcode character(15),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  curr character(5),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  bbtype character(20),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  secweight numeric(19,6),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  securitykey integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  gics_sector integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  gics_sector_name character(75),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  gics_industry_group integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  gics_industry_group_name character(75),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  gics_industry integer,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  gics_industry_name character(75),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  bbdesc character(50),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">  CONSTRAINT pk_tmp_index_member PRIMARY KEY (tmp_index_member_key),</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT fk_tmpindexmember_index_ FOREIGN KEY (index_key)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">      REFERENCES index_ (index_key) MATCH SIMPLE</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">      ON UPDATE NO ACTION ON DELETE NO ACTION,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">  CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">      REFERENCES taskrun (taskrunkey) MATCH SIMPLE</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">      ON UPDATE NO ACTION ON DELETE NO ACTION</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">WITHOUT OIDS;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">ALTER TABLE tmp_index_member OWNER TO postgres;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Kevin Duffy</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">WR Capital Management</span></font></div>

Re: simple SQL query

From

Andreas Joseph Krogh

Date:

29 October 2008, 19:58:46

On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results.  Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs.  ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
>    where securitytypekey NOT IN ( 5,27) and  ISIN IN
>
>   (select ISIN from tmp_index_member )
>
> returns 3069 rows.  This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
>    from security
>
>    where securitytypekey NOT IN (5,27)  and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows.  It returns zero.
>
> select * from tmp_index_member tim
>
>   where tim.ISIN  NOT IN
>
>   (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me.  To do this
> I need the above
>
> query to work.

I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...

> Question:  does a UNIQUE constraint create an index?

Yes.

> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

Re: simple SQL query

From

"Kevin Duffy"

Date:

29 October 2008, 20:56:24

Gentlemen:

Thanks so much for your assistance.

This returns 512 rows.
select * from tmp_index_member tim where tim.ISIN  NOT IN  (select ISIN from security sec     where  ISIN is NOT NULL
and     securitytypekey IS NOT NULL and  securitytypekey NOT IN ( 5,27) ) 

Can someone explain why the NULL ISINs in Security is causing
so much grief?  I do not get it.


KD




-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Joseph Krogh
Sent: Wednesday, October 29, 2008 3:58 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] simple SQL query

On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results.  Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs.  ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
>    where securitytypekey NOT IN ( 5,27) and  ISIN IN
>
>   (select ISIN from tmp_index_member )
>
> returns 3069 rows.  This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
>    from security
>
>    where securitytypekey NOT IN (5,27)  and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows.  It returns zero.
>
> select * from tmp_index_member tim
>
>   where tim.ISIN  NOT IN
>
>   (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me.  To do this
> I need the above
>
> query to work.

I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...

> Question:  does a UNIQUE constraint create an index?

Yes.

> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: simple SQL query

From

Andreas Joseph Krogh

Date:

29 October 2008, 21:50:35

On Wednesday 29 October 2008 21:56:14 Kevin Duffy wrote:
>
> Gentlemen:
>
> Thanks so much for your assistance.
>
> This returns 512 rows.
> select * from tmp_index_member tim
>   where tim.ISIN  NOT IN
>   (select ISIN from security sec
>      where  ISIN is NOT NULL and
>        securitytypekey IS NOT NULL and  securitytypekey NOT IN ( 5,27) )
>
> Can someone explain why the NULL ISINs in Security is causing
> so much grief?  I do not get it.

Sure. BTW; I ment "IS NULL OR securitytypekey NOT IN (5,27)".
Remember that "WHERE col NOT IN (<list>)" doesn't match NULL-values for "col", so these will both return "false" for
NULL-valueof "col": 

WHERE col NOT IN (2,3)
WHERE col = 2

The reason is that NULL is "unknown", so testing against it also returns "unknown"(NULL).

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

Re: simple SQL query

From

Tom Lane

Date:

30 October 2008, 02:31:14

"Kevin Duffy" <KD@wrinvestments.com> writes:
> Can someone explain why the NULL ISINs in Security is causing 
> so much grief?  I do not get it.

NULL generally is taken as "unknown" in SQL comparisons.  So if you have
any nulls in the output of the sub-select, what the upper select sees
is a situation like
where 42 NOT IN (1,2,3, ..., NULL, ...)

Now, if it finds 42 in the subquery output, it can say definitively that
the result of NOT IN is FALSE, because 42 clearly *is* in the output.
However, if it doesn't find a match, then what does that NULL represent?
It's unknown, and therefore whether it's equal to 42 is unknown, and so
the result of the NOT IN is unknown.  And WHERE treats an unknown result
the same as FALSE, so you don't get an output row from the upper query.

NOT IN is generally pretty evil and best avoided: the funny behavior
with nulls makes it not only a trap for novices, but hard for the system
to optimize.  Consider recasting as NOT EXISTS instead.
        regards, tom lane