Compare two large tables?

[mrbti]

I have two tables (table1 & table2) that contain fields ID and SITE. Both tables contain one million (1000000) rows each.

I need to compare the SITE field in the tables to find out what data exisits in the second table that does not exist in the first.

I have tried the following but because of such large tables, it seems to limit to 30 results, take forever or just not work at all.

SELECT site FROM `table1`
WHERE `table1`.site
NOT IN (
SELECT site
FROM `table2`
WHERE `table1`.site = `table2`.site
)

I need a way to output the results into a 3rd table. I am using PHP as the base coding language.

 

[r937]

CREATE TABLE table3 
SELECT table1.site 
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.site = table1.site
 WHERE table2.site IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mrbti]

Thanks - works great