SQL：使用 MySQL 4 获取非耦合项列表

Question

我不太擅长 SQL，并且遇到了一个不知道如何解决的问题。我已经阅读并重新阅读了一本有关 SQL 的书（O'Reilly 的《学习 SQL》）的部分内容，我希望这本书包含我需要的信息，但我还没有找到。

我的问题如下。我将使用一个简化的示例来使其更容易讨论。

我有三个表：car、rim 以及两者的组合：carRim。

car
    carId
    description

rim
    rimId
    description

carRim
    carRimId
    carId
    rimId
    price

在表 carRim 中，我有一个额外的价格属性，因为每种类型的汽车的轮辋价格可能不同。我的一个限制是每种类型的轮辋只能与每种类型的汽车连接一次。因此，所有车圈组合都应该是独一无二的。

如果我想给汽车添加轮辋，我需要一份尚未与该汽车连接的轮辋列表。为此，我认为我需要分别使用 rim 表和 carRim 表来获取已与我想要添加轮辋的汽车耦合的轮辋总列表和 carRim 列表。

我编写了（简单）查询来生成与特定汽车耦合的轮辋列表，在以下示例中为 carId 9 的汽车。

SELECT
    * 
FROM
    rims 
INNER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
WHERE
    carRims.carId = 9 

但现在我需要以下轮辋列表：尚未耦合到特定汽车。问题是，如果我执行 LEFT OUTER JOIN，我得到的列表会被轮辋与其他汽车的耦合“污染”，因此“WHERE carRims.carId IS NULL”的过滤条件不起作用。

SELECT
    * 
FROM
    rims 
LEFT OUTER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
WHERE
    carRims.carId IS NULL

另一个挑战是我无法使用 MySQL 5 中的任何新语法，例如子查询，因为我的客户端正在使用 MySQL 4，目前无法升级。

这个问题的查询可以在MySQL 4中编写吗，我怀疑可以。

谢谢！

Answer 1

SELECT  *
FROM    rims r
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    carRims cr
        WHERE   cr.rimId = r.RimId
                AND cr.carID = 9
        )

更新：

要将NOT EXISTS重写为LEFT JOIN / IS NULL，您需要将所有条件放入ON连接的 子句：

SELECT  r.* 
FROM    rims r
LEFT OUTER JOIN
        carRims cr 
ON      cr.rimId = r.rimId 
        AND cr.crID = 9
WHERE   cr.carId IS NULL

Answer 2

您可以在左外连接 ON 表达式中添加额外的条件。您想要的是 carRims 中与您的 rims 行相匹配的行，并且属于 9 号车，对吗？

SELECT
    * 
FROM
    rims 
LEFT OUTER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
    AND carRims.carId = 9
WHERE
    carRims.carId IS NULL

如果其他汽车还有其他 carRim，它们将被 ON 子句中的额外条件过滤掉。

---

关于在 JOIN 或 WHERE 子句中放置条件的问题：

对于外部连接，将条件放置在何处很重要比较。 JOIN 子句中的条件用于测试一个表中的行是否与另一表中的行匹配。测试匹配后，我们对各个表中的行所做的操作取决于连接的类型。

对于外连接，即使 carRims 中没有匹配的行，我们也需要来自 rims 的行。如果我们将 carID=9 条件放在 WHERE 子句中会怎样？

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID
WHERE c.carID = 9

发生的情况如下：外连接返回 rims 中的所有行，其中 carRims 中与 rimID 匹配的行并且包含 具有虚假 carID 值的行。仅当没有汽车与给定轮辋匹配时，它才会对c.*列使用NULL。

但是，WHERE 子句会消除连接产生的所有行，除非 carID 的值为 9。这意味着它还会消除 carID 所在的位置NULL，即它消除任何与任何汽车都不匹配的轮辋的行。因此，结果等同于 INNER JOIN 的结果。

因此，在将行连接到 rims 之前，我们需要从 carRims 中排除具有错误 carID 的行。

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND c.carID = 9

这是关于该单个表中的哪些行可以与另一个连接表中的行匹配的条件。

许多书籍说您可以在 ON 子句和 WHERE 子句之间自由混合条件。但并非所有情况都是如此。它适用于 INNER JOIN，因为两种方式的最终结果都是相同的。它也适用于仅适用于左连接中左表的条件，例如：

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND r.make = 'ABC Rims'

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID 
WHERE r.make = 'ABC Rims'

对于左外连接中右表的条件，它确实不管你把条件放在哪里。

---

对您所说的内容再发表一条评论：

我的一个限制是每种类型的轮圈只能与每种类型的汽车连接一次。因此，所有车圈组合都应该是独一无二的。

那么您是否已在 carRims(carId,rimId) 上声明了 UNIQUE 约束？

Answer 3

（我需要更多的空间来详细说明，评论没有足够的字符）

@Bill Karwin
您提供的查询是我的问题的正确答案。谢谢！

我想要的是未连接到特定汽车的行（轮辋）列表。

在《学习 SQL》一书中，我读到查询中过滤器和连接条件的位置并不重要。他们给出了一个内部联接的示例，其中有 1 个联接条件（在 ON 子句之后）和 1 个过滤条件（在 WHERE 子句之后）。他们证明，无论将这两个条件放在 ON 子句之后还是 WHERE 子句之后，实际上并不重要。

考虑到这一点，我不明白你的解决方案的一部分。如果条件

carRims.carId = 9

和条件

carRims.carId IS NULL

都存在，那不是会导致结果集为空吗？因为没有字段可以同时为 9 和 NULL 对吗？

我的怀疑是：JOIN 子句中的条件是位置限制的，并且您不能只是将它们放在WHERE 子句中。

这是正确的吗？