mysql - LEFT OUTER JOIN 和 INNER JOIN 使总行数相等

Question

我有一个生产者和产品表。我正在尝试编写 2 个查询，有产品的生产者和没有产品的生产者。

每个查询返回的行应该加起来就是生产者表中的总行数，但我的代码不是这样。两个查询的总和为 819，但 Producer 表中只有 766 行。重复从哪里来？

我的 JOIN 做错了什么？

PRODUCER TABLE
+------+--------+------+------+
| producerID  | producerName  |
+------+--------+------+------+
|  123        | Toys R Us     |
|  234        | GameStop      |
|  345        | Amazon        |
+------+--------+------+------+

PRODUCT TABLE
+------+--------+------+--------+------+
| productID | productName | producerID |
+------+--------+------+--------+------+
|  1        | Mega Man    |  123       |
|  2        | Lemmings    |  234       |
|  3        | Mario Kart  |  234       |
+------+--------+------+--------+------+


/*STORES CARRYING Products*/
/*This query returns 169*/
SELECT producerName, pt.producerID, productName
FROM Product pt 
INNER JOIN Producer pd ON pt.producerID = pd.producerID 

/*STORES NOT CARRYING Products*/
/*This query returns 650 */
SELECT producerName, pt.producerID, productName
FROM Producer pd
LEFT OUTER JOIN Product pt ON pt.producerID = pd.producerID
WHERE pt.producerID IS NULL

/*Count all rows returns 766*/
SELECT COUNT(*) FROM Producer

标签： mysqlleft-joinrelational-database

Answer 1

您可以使用Subquery而不是使用 Join 。

例如，此查询将返回所有拥有产品的生产者。

SELECT *
FROM Producer
WHERE producerID IN (SELECT producerID FROM Products)

此查询将返回所有没有产品的生产者。

SELECT *
FROM Producer
WHERE producerID NOT IN (SELECT producerID FROM Products)