CODEWITHSUNDEEP
mysql
user1788736
user1788736

Reputation: 2845

How to find difference between two MySQL tables using MySQL statement?

I have two tables called MytableA and MytableB. MytableA holds 609 rows and MytableB holds 607 rows. Both Rows have same structure. I just want to find out what imageURL(s) exist in MytableA that don't exist in MytableB and display those missing rows? Could anyone tell me what is the MySQL statement to display those rows that are missing in MytableB?

CREATE TABLE IF NOT EXISTS `MytableA` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `bio` varchar(90) DEFAULT NULL,
  `website` varchar(90) DEFAULT NULL,
  `profile` varchar(90) NOT NULL,
  `fullname` varchar(250) DEFAULT '',
  `diffId` varchar(250) NOT NULL DEFAULT '',
  `imageUrl` varchar(250) NOT NULL DEFAULT '',
  `PageURL` varchar(250) NOT NULL DEFAULT '',
  `CreatedTime` varchar(250) NOT NULL DEFAULT '',
  `imageTags` varchar(250) NOT NULL DEFAULT '',
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ID`)
)

Upvotes: 1

Views: 14866

Answers (2)

Sergey Nikolaev
Sergey Nikolaev

Reputation: 745

I think you're looking for something like this. It might be better and faster then subselect.

SELECT * 
FROM tableA 
LEFT JOIN tableB ON tableA.field = tableB.field 
WHERE tableB.field is NULL;

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754920

SELECT *
  FROM MyTableA
 WHERE imageURL NOT IN (SELECT imageURL FROM MyTableB)

There may be faster ways to do it, but this has the merit of succinctness.

Upvotes: 10

Related Questions