mysql - 数据丢失时删除行 2 joins away

Question

我习惯于结合使用连接和“IS NULL”来删除行，比如 this

示例:

A 有第 1 类数字 1。B 有第 2 类数字 2，但也有第 1 类数字 1。

查询:

SELECT people.name,
 GROUP_CONCAT(phone_numbers.phone_number) AS "All number",
 GROUP_CONCAT(IF(phone_numbers.type_id = 2, phone_numbers.phone_number, NULL)) AS "Type 2 Numbers" 
FROM people 
LEFT JOIN people_phones USING (people_id) 
LEFT JOIN phone_numbers USING (phone_number) 
GROUP BY people_id;

结果:

+------+------------+----------------+
| name | All number | Type 2 Numbers |
+------+------------+----------------+
| A    | 1          | NULL           |
| B    | 1,2        | 2              |
+------+------------+----------------+

我想删除所有没有类型 2 号码的人，在本例中为 A。

首先我尝试了通常的方法(如果它的一个加入就有效)

尝试过:

DELETE people 
FROM people 
LEFT JOIN people_phones USING (people_id) 
LEFT JOIN phone_numbers 
  ON (phone_numbers.phone_number = people_phones.phone_number
      AND phone_numbers.type_id = 2) 
WHERE phone_numbers.phone_number IS NULL;

但这会同时删除人员“A”和人员“B”，因为“B”的数字不是类型 2。

如何删除没有 2 类号码的人？
(真实的例子有更多的连接，更复杂的 where 部分，表中有 5 百万行。)

用于重新创建示例数据的 SQL:

CREATE TABLE people (people_id INT NOT NULL AUTO_INCREMENT KEY, name TINYTEXT NOT NULL);
CREATE TABLE phone_numbers (phone_number INT NOT NULL KEY, type_id INT NOT NULL);
CREATE TABLE people_phones (people_id INT NOT NULL, phone_number INT NOT NULL, PRIMARY KEY(people_id, phone_number))

INSERT INTO people VALUES (1,'A'),(2,'B');
INSERT INTO phone_numbers VALUES (1, 1), (2,2);
INSERT INTO people_phones VALUES (1,1), (2,1), (2,2);

Answer 1

这是一种方式:

DELETE p
FROM people AS p
WHERE p.people_id NOT IN (SELECT pp.people_id
                          FROM people_phones as pp
                          INNER JOIN phone_numbers as pn
                             ON pp.phone_number = pn.phone_number
                          WHERE pn.type_id = 2);

另一种方式:

DELETE p
FROM people AS p
WHERE NOT EXISTS(SELECT 1 
                 FROM people_phones as pp
                 INNER JOIN phone_numbers as pn
                    ON pp.phone_number = pn.phone_number
                 WHERE pn.type_id = 2
                 AND pp.people_id = p.people_id);

Here is a sqlfiddle带有第二个版本的演示(这也是我推荐的那个)。