三个 MySQL 表的左连接和 NOT

Question

在MySQL数据库中，我有三个连接的表：文本链接到版本，它链接到中间表editor_attestations(我认为第四个表编辑器在这里无关紧要)。通常每个文本有多个版本，反过来每个版本通常有几个editor_attestations(通过联合出版)。

如果我想使用特定的编辑器获取所有文本和版本，这工作正常：

SELECT texts.text_id FROM texts
LEFT JOIN editions ON texts.text_id = editions.text_id
LEFT JOIN editor_attestations ON editions.edition_id = editor_attestations.edition_id
WHERE editor_attestations.editor_id = 102

现在我已经尝试了几种方法，让所有文本未被 editor_id = 102 编辑，但似乎没有一种有效。

SELECT texts.text_id FROM texts
LEFT JOIN editions ON texts.text_id = editions.text_id
LEFT JOIN editor_attestations ON editions.edition_id = editor_attestations.edition_id
WHERE (editor_attestations.editor_id != 102 or editor_attestations.editor_id is null)

在结果中有许多记录，其中editor_id 102 与其他人合作，因此 editor_attestations.editor_id ！= 102 成立。但我想排除这些。

我只想在表 1(文本)中包含与表 3 (editor_attestations) 中的特定值没有任何关联的值。

我已经尝试使用子查询的 NOT IN 或 NOT EXIST，但这不起作用。

我觉得它不应该那么复杂...

Answer 1

由于您只需要texts.text_ids，因此您可以按texts.text_id分组并在 HAVING 子句中设置条件。

SELECT texts.text_id 
FROM texts
LEFT JOIN editions ON texts.text_id = editions.text_id
LEFT JOIN editor_attestations ON editions.edition_id = editor_attestations.edition_id
GROUP BY texts.text_id 
HAVING SUM(editor_attestations.editor_id = 102) = 0

Answer 2

forpas和xavier的答案展示了可行的方法。

作为使用NOT EXISTS的方法的演示

SELECT t.text_id 
FROM texts t
WHERE NOT EXISTS ( SELECT 1 
FROM editions e
JOIN editor_attestations a 
ON a.edition_id = e.edition_id
WHERE a.editor_id = 102 
AND e.text_id = t.text_id
)

请注意子查询e.text_id = t.text_id中将子查询与外部查询相关联的谓词。

此方法允许返回texts中的其他列，而无需复制使用 JOIN 操作可能出现的行。

对于大型集合，需要合适的索引以获得最佳性能。

---

另一种方法是使用反联接模式：

SELECT t.text_id 
FROM texts t
-- match to editor_id=102
LEFT
JOIN ( SELECT e.text_id 
FROM editions e
JOIN editor_attestations a
ON a.edition_id = e.edition_id
WHERE a.editor_id = 102
GROUP BY e.text_id
) s
ON s.text_id = t.text_id
-- exclude rows that had a match
WHERE s.text_id IS NULL

内联视图查询s为我们获取与editor_id=102相关的text_id的独特列表。 antijoin 是此查询返回的左外部连接，因此它返回所有行文本，以及来自s的匹配行。诀窍是 WHERE 子句中的一个条件，它排除了具有匹配的行(如果s中有匹配的行，我们保证s.text_id将是非空的，因为只有非空值才能满足连接条件 (ON s.text_id =中的相等比较)。 如果我们排除这些行，我们将留下来自texts的行，这些行没有任何来自s的匹配行。

Answer 3

一个非常简单的解决方案是：我查找所有被某个编辑器修改过的文本，然后我选择补充集。它可能不是很有效，但对于一个小型数据库，它可以工作：

SELECT texts.text_id FROM texts where texts.text_id NOT IN
(SELECT editions.text_id FROM editions
JOIN editor_attestations ON editions.edition_id = editor_attestations.edition_id
WHERE editor_attestations.editor_id = 102
)