Re: Non Matching Records in Two Tables

On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote: <blockquote type="CITE"><pre>
<font color="#000000">> Here is my query SQL:</font>
<font color="#000000">> </font>
<font color="#000000">> SELECT key100 FROM ncccr10</font>
<font color="#000000">> WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);</font>
<font color="#000000">> </font>
<font color="#000000">> It is is running after 30 minutes. Here is the query plan:</font>
<font color="#000000">></font>

<font color="#000000">I would try an outer join:</font>

<font color="#000000">select a.key100</font>
<font color="#000000">from ncccr10 a</font>
<font color="#000000">left join ncccr9 b on( key100 )</font>
<font color="#000000">where b.key100 is null;</font>

<font color="#000000">also (hate to be obvious) have you analyzed lately?</font>


<font color="#000000">__________________________________________________</font>
<font color="#000000">Do You Yahoo!?</font>
<font color="#000000">Tired of spam?  Yahoo! Mail has the best spam protection around </font>
<font color="#000000"><a href="http://mail.yahoo.com">http://mail.yahoo.com</a> </font>

<font color="#000000">---------------------------(end of broadcast)---------------------------</font>
<font color="#000000">TIP 1: if posting/reading through Usenet, please send an appropriate</font>
<font color="#000000">       subscribe-nomail command to <a
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>so that your</font>
 
<font color="#000000">       message can get through to the mailing list c</font>leanly
</pre></blockquote> Yes, I did a VACUUM ANALYZE on the entire database before running this query. Also, I get an error
withyour suggestion:<br /><br /> csalgorithm=# SELECT a.key100 FROM ncccr10 a<br /> csalgorithm-# LEFT JOIN ncccr9 b
ON(a.key100)<br/> csalgorithm-# WHERE b.key100 IS Null;<br /> ERROR:  argument of JOIN/ON must be type boolean, not
typecharacter<br /><br />