Re: Non Matching Records in Two Tables

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 the first 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. This should 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 the original 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 table2 or in table2 that don't have a corresponding row in table1; for that specific requirement you may actually be better off doing two queries (one for each table) and adding the results together.

-Owen