sql - 如果 id 不存在于另一个表中，则从表中删除

Question

我想从 types 中删除 ID在 types_photos 中找不到的但我不知道如何才能做到这一点。 id_type在 types_photos与 id 相同在 types .表的结构如下所示:

CREATE TABLE IF NOT EXISTS `types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_added` int(11) DEFAULT '0',
  `id_user_edited` int(11) DEFAULT '0',
  `data_name` text NOT NULL,
  `data_name_seo` text NOT NULL,
  `data_type` enum('tag','equipment','search') NOT NULL,
  `datetime_added` datetime NOT NULL,
  `datetime_edited` datetime NOT NULL,
  `ipaddress_added` text NOT NULL,
  `ipaddress_edited` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

CREATE TABLE IF NOT EXISTS `types_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_added` int(11) DEFAULT '0',
  `id_user_edited` int(11) DEFAULT '0',
  `id_type` int(11) DEFAULT '0',
  `id_photo` int(11) DEFAULT '0',
  `datetime_added` datetime NOT NULL,
  `datetime_edited` datetime NOT NULL,
  `ipaddress_added` text NOT NULL,
  `ipaddress_edited` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

所以，我的问题是；如何从 types 中删除所有 ID在 types_photos 中找不到的?

Answer 1

DELETE FROM types 
WHERE id NOT IN (
  SELECT ID FROM types_photos
)