mysql - 试图反转 WHERE 子句，但它没有加起来

Question

我正在运行一个 SQL 查询来显示一些表的结果。没有 WHERE 子句的查询返回 1087 个结果

SELECT hf.id, hf.`nhs_choices_organisationid`,hf.`sites_organisation_code`,hf.`eric_site_id` FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`

如果我在中添加 WHERE 子句，我会得到 260 个结果

SELECT hf.id, hf.`nhs_choices_organisationid`,hf.`sites_organisation_code`,hf.`eric_site_id` FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`
WHERE (REPLACE( (REPLACE( s.NAME, "'", '')), " ", '')) LIKE (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", ''))AND  (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", '')) LIKE (REPLACE( (REPLACE( es.site_name, "'", '')), " ", ''))

现在我想做的是找到所有不在 where 子句中的结果。对我来说，第一个明显的解决方案是将其括在方括号中并在其前面加上 NOT

SELECT hf.id, hf.`nhs_choices_organisationid`,hf.`sites_organisation_code`,hf.`eric_site_id` FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`
WHERE NOT ((REPLACE( (REPLACE( s.NAME, "'", '')), " ", '')) LIKE (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", ''))AND  (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", '')) LIKE (REPLACE( (REPLACE( es.site_name, "'", '')), " ", '')))

但这只返回了 370 个结果

这次我试图明确地做到这一点

SELECT hf.id, hf.`nhs_choices_organisationid`,hf.`sites_organisation_code`,hf.`eric_site_id` FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`
WHERE (REPLACE( (REPLACE( s.NAME, "'", '')), " ", ''))  NOT LIKE (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", ''))OR  (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", '')) NOT LIKE (REPLACE( (REPLACE( es.site_name, "'", '')), " ", ''))OR (REPLACE( (REPLACE( es.site_name, "'", '')), " ", '')) NOT LIKE (REPLACE( (REPLACE( s.NAME, "'", '')), " ", ''))

再次只得到 370。

现在根据我对数学基础的理解1087-260≠370。

我可以将其全部写到返回预期 827 的子查询中，但有必要吗？

SELECT hf.id, hf.`nhs_choices_organisationid`,hf.`sites_organisation_code`,hf.`eric_site_id` FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`
WHERE hf.id NOT IN
(SELECT hf.id FROM `hospital_final` hf
left join nhs_choices nc on nc.OrganisationID = hf.`nhs_choices_organisationid`
left join sites s on s.`Organisation Code` = hf.`sites_organisation_code`
left join eric_site es on es.site_code = hf.`sites_organisation_code`
WHERE (REPLACE( (REPLACE( s.NAME, "'", '')), " ", ''))  LIKE (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", ''))AND  (REPLACE( (REPLACE( nc.OrganisationName, "'", '')), " ", '')) LIKE (REPLACE( (REPLACE( es.site_name, "'", '')), " ", '')))

Answer 1

您有一些复杂的 WHERE 表达式，但这并不重要。重要的是:

WHERE <something>

不是

WHERE NOT <something>

那是因为表达式的计算结果可以为 NULL —— 对于外连接尤其如此。

正确的逆应该是:

WHERE (NOT <something> OR
       <something> IS NULL
      )