SQL statement to tell difference between two tables

michaelshavel
michaelshavel used Ask the Experts™
on
Hi,
I am running MySql 4.1.22
I have two tables  A and B.

A has a lot of customer data. ID is the key.
B has geocoded data that I enter in every day via a script (lat and long). ID is the key.

For each new customer in A, I insert geocoded data in B.

I need a query that will tell me what ID's in table A,  DO NOT exist in Table B.

I can manage this now with some scripting and a few queries but I'm sure this can be done with just one query.

I have tried the query below in the code area but I keep getting zero result set. I am sure that table A has some ID's that do not yet exist in table B

Suggestions?


Thanks,

Mike



SELECT A.ID FROM A,B  WHERE A.ID=B.ID AND B.ID IS NULL;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
SELECT A.ID
FROM A LEFT JOIN
      B ON A.ID=B.ID
WHERE B.ID IS NULL
SELECT * FROM A
EXCEPT
SELECT * FROM B

--Rows (A without B)

Author

Commented:
I tried the EXCEPT example but apparently my version of Mysql doesn't support it because I get an syntax error message.

I tried the LEFT JOIN example but it doesn't return  in over 5 mins so I killed the query.   Does a JOIN take particularly long to run? I have about 35,000 records in table A and about 33,000 in table B. How long should I expect a JOIN to take in a case like that?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Doug BishopDatabase Developer
Commented:
There is nothing wrong with the LEFT JOIN syntax and is actually very common code for the type of scenario you are looking for. Depending on your table sizes it may take a while to run. Be patient.

Also, if you expect a large number of rows in tbale A that aren't in table B, you might save the results to a table.

SELECT A.ID
INTO MissingIDs
FROM A LEFT JOIN
      B ON A.ID=B.ID
WHERE B.ID IS NULL
Top Expert 2010

Commented:
michaelshavel,

Do you have indexes set up on A.ID and B.ID?  If not, try creating those indexes and then running the query.

Patrick

Author

Commented:
Thanks everyone. The JOIN worked fine when I tried it on a smaller dataset. It just takes some time to run on the large set.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial