CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
mathew
mathew

Reputation: 65

How do I compare two tables data?

Hi I do have an old table and new table with same index/data. TABLE1 and TABLE2

but TABLE1 has got more data than TABLE2. this was maintained by some one and I dont know how this happened. so my question is how do I compare these two table and find which data is TABLE2 missing?? there is almost 200000 datas there so manually doing is not possible...

Upvotes: 0

Views: 4394

Answers (4)

bensiu
bensiu

Reputation: 25564

in PHP:

http://us.php.net/manual/en/function.array-diff.php

in SQL:

SELECT * FROM TABLE1 WHERE id {NOT} IN ( SELECT id FROM TABLE2 )

depending on criteria of comparison

Upvotes: 1

jon_darkstar
jon_darkstar

Reputation: 16768

By same index I am hoping you mean they share a primary key?

SELECT * FROM TABLE1 WHERE username NOT IN (SELECT username FROM TABLE2)

Upvotes: 0

jinowolski
jinowolski

Reputation: 2472

Solution without nested query:

select TABLE1.id from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id where TABLE2.id is null

Upvotes: 0

Hoàng Long
Hoàng Long

Reputation: 10848

Did you mean something like this:

SELECT * FROM TABLE1 t1 WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE t1.id == t2.id)

Upvotes: 0

Related Questions