Ken Hill wrote:
> I need some help with a bit of SQL. I have two tables. I want
> to find records in one table that don't match records in another
> table based on a common column in the two tables. Both tables
> have a column named 'key100'. I was trying something like:
>
> SELECT count(*)
> FROM table1, table2
> WHERE (table1.key100 != table2.key100);
>
> But the query is very slow and I finally just cancel it. Any help
> is very much appreciated.
That's a cartesian join, there, and it'll be huge (on the order of N*M rows, where N and M are the number of rows in
thefirst and second tables respectively).
It sounds like, from your description, you want to find rows in table1 that don't have a corresponding row in table2.
Thisshould work:
SELECT count(*) FROM table1 LEFT JOIN table2 ON table1.key100 = table2.key100 WHERE table2.key100 IS NULL;
This will still be fairly slow unless there are indexes on table1.key100 and table2.key100, but nowhere near as slow as
theoriginal query.
Frank Bax's solution will work if what you want is a count of rows in table1 that don't have a corresponding row in
table2or in table2 that don't have a corresponding row in table1; for that specific requirement you may actually be
betteroff doing two queries (one for each table) and adding the results together.
-Owen