How to get All Records in Table A that don't havematching ID in Table B

[Photoshare]

I thought this would be a breeze, but I cannot seem to get it to work.

I have two tables (Table A) and (Table B).
(Table A) = all productid's (15,000 records)
(Table B) = Limited # of productid's (12,700 records)

I have had no success writing a query that will return ONLY the records from
Table A that do not exist in Table B. I have tried sub-queries and "Not Equal"
Joins but both seem to hang or crash MySQL. Does anyone know of code that
would work or is the amount of records to compare too large?

Thank you in advance.

Ron
[email]insight32@aol.com[/email]

[reenaroy]

Try this SQL

select * from tableA where id not in(select id from tableB)

[JMGibson3]

That example will work but might be pretty slow depending on DB, indexes, etc.
Your mission is usually accomplished with a "left join". Syntax depends on
your DB, for example, Oracle doesn't even use the Join keyword, they prefer a
(+) in the WHERE clause. If it's taking a long time, try a Left Join,
something like:

Select whatever from tblA
LEFT JOIN tblB on tblA.ID = tblB.ID
WHERE tblB.anycolumnatall IS NULL

[Dan Bracuk]

Originally posted by: JMGibson3
That example will work but might be pretty slow depending on DB, indexes, etc.
Your mission is usually accomplished with a "left join". Syntax depends on
your DB, for example, Oracle doesn't even use the Join keyword, they prefer a
(+) in the WHERE clause. If it's taking a long time, try a Left Join,
something like:

Select whatever from tblA
LEFT JOIN tblB on tblA.ID = tblB.ID
WHERE tblB.anycolumnatall IS NULL

Oracle started supporting the join keyword with version 9i.

Your point about speed is valid though.

[paross1]

What version of MySQL are you using? Older versions (before 4.1) did not allow
subqueries, which would explain a lot. If you are version 4.1 or later, then I
recommend using NOT EXISTS rather than NOT IN, as this is generally more
efficient (at least it is in Oracle).

Example:
SELECT a.productID, a.other_fields
FROM tableA a
WHERE NOT EXISTS( SELECT 1
FROM tableB b
WHERE a.productID = b.productID)

Phil