On 9 May, 23:31, user <u...@127.0.0.1> wrote:
> On Fri, 09 May 2008 04:44:55 -0700, superman183 wrote:
> > Hi,
>
> > Hoping someone can assist me with a query I have.
>
> > I have two tables, both of which have at least one field of the same
> > name, type, etc, etc. This field I will call SKEY, for the purposes of
> > this post. SKEY is a CHAR field, 4 chars in length.
>
> > One of the tables, we'll say Table1, has the complete list of available
> > SKEY's. Table2, on the other hand, contains only some of the available
> > SKEY's.
>
> > I want to be able to find out which SKEY's are missing from Table2; ie.
> > which SKEY's are in Table1, but not in Table2.
>
> > I have tried several different queries, but generally end up in a bit of
> > a mess, more often returning tens of thousands of results, when in fact
> > there are only a couple of hundred records in total.
>
> > My pseudo-code would be something like:
>
> > SELECT SKEY FROM TABLE1 WHERE TABLE1.SKEY NOT LIKE TABLE2.SKEY
>
> > The basic query which I tried is:
>
> > SELECT TABLE1.SKEY FROM TABLE1 JOIN TABLE2 WHERE TABLE1.SKEY NOT LIKE
> > TABLE2.SKEY
>
> > I also tried a few variations, with DISTINCT, etc, etc ... but I never
> > end up with anything close to what I need....
>
> Have you tried this code?
>
> select skey from table1, table2 where table1.skey not in table2.skey
>
> Or, pulling your complete second table as a sub-query (I think this may
> be needed; it's been a while):
>
> select a.skey from table1 a, (select distinct skey from table2) b where
> a.skey not in b
>
> Luck

Luck, good name

A lot of luck will be needed to get your first proposal to work.

As to your second one, Rik's solution does it far more efficiently.

As a general rule, use JOINs (or LEFT JOINs) wherever possible and
avoid sub-selects such as yours.

Also, always code explicit JOINs rather than implicit comma joins.