Find rows with bad referential integrity, demonstration

gistfile1.sql

-- Create tables

CREATE TABLE `fixtures_regress_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fixtures_regress_book_cc846901` (`author_id`),
  CONSTRAINT `author_id_refs_id_7fdd0933` FOREIGN KEY (`author_id`) REFERENCES `fixtures_regress_person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

CREATE TABLE `fixtures_regress_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- insert records

SET foreign_key_checks=0;
INSERT INTO `book` (`id`, `name`, `author_id`) VALUES (1, 'Cryptonomicon', 3);
INSERT INTO `person` (`id`, `name`) VALUES (4, 'Neal Stephenson');
SET foreign_key_checks=1;

-- find records in table with missing references
-- test_ere_testproj is the DB name

SELECT COUNT(*) FROM `test_ere_testproj`.`fixtures_regress_book` as REFERRING
LEFT JOIN `test_ere_testproj`.`fixtures_regress_book` as REFERRED
ON (REFERRING.`author_id` = REFERRED.`id`)
WHERE REFERRING.`author_id` IS NOT NULL
AND REFERRED.`id` IS NULL