Comparison of two tables in MYSQL
Solution 1
Try this:
SELECT table1.*
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL
LEFT OUTER JOIN
link two table starting by table1, if table2 has no linked row all fields of table2 will be null. So, if you put in your WHERE
condition table2.id is null, you get only rows in table1 not existing in table2
Solution 2
You could solve this by doing a left outer join and checking for all rows that don't exist. Try the following depending on if you want to find values not existent from table1 in table2 or table2 in table1.
SELECT *
FROM table1
LEFT OUTER JOIN table2 ON (table1.id = table2.id)
WHERE table2.id IS NULL;
SELECT *
FROM table2
LEFT OUTER JOIN table1 ON (table1.id = table2.id)
WHERE table2.id IS NULL;
SQL Fiddle: http://sqlfiddle.com/#!2/a9390/8
Solution 3
well, if you want the answer in PHP, then here is it:
$sql=mysql_query("SELECT * FROM table1");
while($row=mysql_fetch_array($sql))
{
$id=$row['id'];
$sql2=mysql_query("SELECT * FROM table2 WHERE id='$id'");
$check=mysql_num_rows($sql2);
if($check==0)
{
echo $id." is not in table1<br>";
}
}
I hope this help you
Solution 4
Make use of this query:
SELECT
*
FROM
table2
LEFT JOIN
table1
ON
table2.primary_key = table1 .primary_key
WHERE
table1 .primary_key IS NULL
;
nermik
Updated on July 04, 2021Comments
-
nermik almost 3 years
I have two tables. One table (table1) has 28500 rows and the another (table2) has 17450 rows. I would like to compare these tables and find rows that do not exist in table1.
SELECT * FROM table1 WHERE ID NOT IN (SELECT DISTINCT(ID) FROM table2)
Any suggestions?
-
Joe Taras almost 11 yearstable2.id = NULL is not correct, please use IS NULL when you compare a field with a NULL value
-
Freddy Sidauruk over 6 yearswhat this means SELECT table1.*
-
Joe Taras over 6 yearsAll fields of table1
-
IncreMan over 5 yearsThis is a bad approach as if you have thousands of rows in table1 you will iterate over each one when running query of table2. It is extremely not efficient
-
131 over 5 yearsTrue. Tells alot about me 5 years ago :D
-
IncreMan over 5 yearsHahah... you are right, I am also feels like that when looking back at my previously written code. It means we are growing in our skills ;)
-
131 over 5 yearsYea, we are :-)
-
KD.S.T. about 4 yearswell, ill be damn. it really works! thank you very much!