帮助将子查询转换为带有联接的查询

Question

我陷入了一个带有连接的查询。客户端站点运行的是 mysql4，因此不能选择子查询。我尝试使用联接重写的效果不太好。

我需要选择承包商表中列出的所有承包商，这些承包商不在具有给定标签 ID 和标签 ID 的承包商2标签表中。县 ID。然而，它们可能会被列在 Contractors2label 与其他标签和县 ID。

表：承包商

cID（主要，自动编号）
公司（varchar）
...等等...

表：contractors2label

身份证号
标签ID
县ID
psID

这个带有子查询的查询有效：

SELECT company, contractors.cID
   FROM contractors
   WHERE contractors.complete = 1
   AND contractors.archived = 0
   AND contractors.cID NOT IN (
     SELECT contractors2label.cID FROM contractors2label
        WHERE labelID <> 1 AND countyID <> 1
   )

我认为这个带有联接的查询是等效的，但它不返回任何结果。手动扫描数据显示我应该得到 34 行，这就是上面的子查询返回的内容。

SELECT company, contractors.cID
   FROM contractors 
   LEFT OUTER JOIN contractors2label ON contractors.cID = contractors2label.cID
   WHERE contractors.complete = 1
   AND contractors.archived = 0
   AND contractors2label.labelID <> 1
   AND contractors2label.countyID <> 1
   AND contractors2label.cID IS NULL

Answer 1

进行LEFT JOIN时，需要将JOIN的所有条件放入ON子句中。

在您的示例中，对于不存在的左连接列，您会得到 NULL ，但随后您再次将它们与值进行比较（<> 1），这不起作用。

SELECT c.company, c.cID
   FROM contractors c
   LEFT JOIN contractors2label c2
          ON ( c2.cID = c.cID AND c2.labelID <> 1 AND c2.countyID <> 1 )
   WHERE c.complete = 1
   AND c.archived = 0
   AND c2.cID IS NULL

顺便说一句：使用别名（如我的示例中的 c）可以使读取和编写查询变得更容易。

Answer 2

当您使用 LEFT 连接的表中的列来限制 where 子句时，您实际上是删除了连接的 LEFT OUTER 部分，因为您正在过滤列那必须在那里。请尝试这样做：

SELECT company, contractors.cID
   FROM contractors 
   LEFT OUTER JOIN contractors2label 
       ON (contractors.cID = contractors2label.cID
           AND contractors2label.labelID <> 1
           AND contractors2label.countyID <> 1)
   WHERE contractors.complete = 1
   AND contractors.archived = 0
   AND contractors2label.cID IS NULL

这将限制作为连接的一部分，因此空值仍然可以在较大的查询中使用。