Database: Comparing values in two different tables

[Crisstina]

Hello all,

I hope someone can answer this question!

I am trying to compare two columns from a table with one column from another table. I need to have the rows from the first table that aren't contained in the second table.

The "problem" is that I have around 800K records on each table.

Has anyone tried to do this in a home computer (2 Gb RAM 1.79 GHz Centrino Duo)?

If you have, how long did it take?

If you haven't, what's your guess? How long will it take?

The DBMS is MySQL running on Windows.

Thank you all!!!

[vass0922]

how long it will take is heavily dependant on if the columns are indexed

I think mysql now supports 'join' syntax?

you could do something as simple as
select *
from t1
left outer join t2
on t1.column1 = t2.column1

obviously changing the table/column names

this is written for t-sql so some minor syntax changes maybe necessary for mysql

what you're asking for is a pretty common outer join
it maybe a right outer join, so if left doesn't give you the correct results try a right outer join

[Rootstonian]

 I've always done:
select
t1.column1,
t1.column2
from table t1
where not exists (select 'x'
                              from table t2
                              where t1.column1 = t2.column
                             and      t1.column2  = t2.column)