Re: Simple SQL Puzzle

chrisjbrady_at_yahoo.com (Chris Brady) wrote in message news:<3be167db.16960542_at_news.baplc.com>...
> Folks - thank you very much evereyone for you help. Indeed using MINUS
> solution appears to have worked!!
>
> I tried
>
> SELECT DISTINCT(i.myfield) FROM table1 i
> WHERE i.myfield NOT IN
> (SELECT w.myfield FROM table2 w)
>
> and this took 8 hours to run - there are 4.5 millions records in
> table1 and 5,000 in table2.
>
> Then I tried
>
> SELECT DISTINCT(i.myfield) FROM table1 i
> MINUS
> SELECT DISTINCT(w.myfield) FROM table2 w
>
> and this took only 30 mins.
>
> Now I THINK that the table2 above is a pure subset of table1. So the
> above produced the records I needed.
>
> However my question now is that if table1 and table2 overlap in the
> conventional Venn diagram kind of way

Are there other ways of overlapping?

In any case, overlap is irrelevant. select a minus select b will return those a that are not in b whether or not they are in both:

Suppose a is 3 4 5 6 7

If b is            then a-b is
1 2                3 4 5 6 7
2 3                  4 5 6 7 *
5 6                3 4     7
7 8                3 4 5 6
8 9                3 4 5 6 7

See *. 3 is in both a and b. a-b should not return 3, right?

Dave. Received on Fri Nov 02 2001 - 09:40:14 CST