Re: Simple SQL Puzzle

Niall - many thanks for responding so quickly. In fact the reason I asked is that I need to extract data from one table (table1) that is not in table2. Both tables have identical structure but the contents of table2 is a subset of table1.

However I had a suspicion that this didn't work as expected, hence the original question:

SQL> select count(distinct(i.myfield)) from table1 i, table2 w

     where i.myfield <> w.myfield;

Instead I've used:

SQL> select distinct(i.myfield) from table1 i

     where i.myfield not in
     (select distinct(w.myfield) from table2 w);

The only problem with half a million records in table1 and a few thousand in table 2 is that this take three hours to run!! I think that this does the second select for every record from the first select!!

By-the-way I'm a newbie to Oracle and SQL as you might have realised!!

Chris B.

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3bdd3db5$0$225$ed9e5944_at_reading.news.pipex.net>...
> Comments below
> "Chris Brady" <chrisjbrady_at_yahoo.com> wrote in message
> news:51604466.0110290314.226d2546_at_posting.google.com...
> > Please can someone explain this? Its the same table compared with
> > itself. The first case I can understand but why is the second case
> > giving the same total? I would have thought that it would be zero.
> <snip>
> > SQL> select count(distinct(i.myfield)) from temp_data i, temp_data w
> > where i.myfield <> w.myfield;
> >
> > COUNT(DISTINCT(I.MYFIELD))
> > --------------------------
> > 47548
>
> Your table has 47548 distinct values of myfield. Each of those is clearly
> not equal to the other 47547 values in the table. Thus each value of myfield
> will satisfy your where clause at least 47547 times. Assuming myfield is not
> in fact unique try taking the distinct keyword out of the two pieces of sql
> to see the results more clearly.
Received on Tue Oct 30 2001 - 06:17:14 CST