mysql - 如何比较具有多个联合的表并加入？

Question

我正在尝试查找仅存在于一个表中的行。大表名称(包含所有行)是kwf_uploads，小表出现在许多名称中。我的目标是找到存在于 kwf_uploads 中但不存在于其他行中的行(此示例是没有关系的表中的主键和外键)。

我在 sql 中做了什么:

select id from
kwf_uploads
left join

(SELECT picture_id as id
FROM documents where picture_id is not null
UNION 
SELECT file_id as id
FROM books where file_id is not null
UNION 
SELECT picture_id as id
FROM employee where picture_id is not null
UNION 
SELECT file_id as id
FROM flightFiles where file_id is not null
UNION 
SELECT picture_id as id
FROM tasks where picture_id is not null
UNION 
SELECT picture_id as id
FROM trainingContentQuestions where picture_id is not null
UNION 
SELECT picture_id as id
FROM trainingQuestions where picture_id is not null) foo  

ON kwf_uploads.id = foo.id

找到它:SQL: cascade UNION and JOIN

但失败并出现错误:SQL (1052): Column "id"in field list is ambiguous。

我不想对每个表都使用连接，因为我不擅长连接，而且 sql 变得非常大且不可读。我也试过 not exists contrusion 没有任何结果。我想我们可以找到更好的解决方案 =)

select id from kwf_uploads 查询的结果(9690 行):

联合查询的结果(6096 行):

结果我想看到 3594(9690 减去 6096)行。

Answer 1

UNION(单独使用时，没有 ALL)是一个“昂贵”的操作。

还有一个替代方案，NOT EXISTS。这种构造是一种“半连接”，我怀疑它可能比联合方法更便宜。

SELECT
      id
FROM kwf_uploads AS u
WHERE NOT EXISTS     (SELECT NULL FROM documents WHERE u.id = picture_id )
      AND NOT EXISTS (SELECT NULL FROM books WHERE u.id = file_id )
      AND NOT EXISTS (SELECT NULL FROM employee WHERE u.id = picture_id )
      AND NOT EXISTS (SELECT NULL FROM flightFiles WHERE u.id = file_id )
      AND NOT EXISTS (SELECT NULL FROM tasks WHERE u.id = picture_id )
      AND NOT EXISTS (SELECT NULL FROM trainingContentQuestions WHERE u.id = picture_id )
      AND NOT EXISTS (SELECT NULL FROM trainingQuestions WHERE u.id = picture_id )