MySQL - 使用 LEFT JOIN 会产生意想不到的结果

Question

我有两个表 orderedproducts 和 productsinstocks。这是表结构:

订购产品表:

+-----+---------+-----------+----------+--------------+
| id  | orderid | productid | quantity | productgroup |
+-----+---------+-----------+----------+--------------+
| 117 | 1016186 |    265385 |        5 | Main         |
| 118 | 1016186 |    407454 |        6 | Main         |
| 119 | 1016186 |    975045 |        7 | Main         |
| 120 | 1016186 |    975046 |        8 | Main         |
+-----+---------+-----------+----------+--------------+

productsinstocks 表:

+----+---------+-----------+----------+
| id | stockid | productid | quantity |
+----+---------+-----------+----------+
| 20 |  903958 |    265385 |      200 |
| 21 |  903958 |    407454 |      205 |
| 22 |  903958 |    975045 |      210 |
+----+---------+-----------+----------+

如您所见，库存在 orderedproducts 表中只有三种类型的产品。我想 JOIN 这些表以获得以下结果:

+---------+-----------+---------+-----------+------------+
| orderid | productid | stockid | order_qty | stock_qty  |
+---------+-----------+---------+-----------+------------+
| 1016186 |    265385 |  903958 |         5 |       200  |
| 1016186 |    407454 |  903958 |         6 |       205  |
| 1016186 |    975045 |  903958 |         7 |       210  |
| 1016186 |    975046 |  903958 |         8 |       NULL |
+---------+-----------+---------+-----------+------------+

我使用了以下查询:

SELECT op.orderid, op.productid, plo.stockid, op.quantity order_qty,  plo.quantity stock_qty FROM orderedproducts op
LEFT JOIN productsinstocks plo
ON op.productid=plo.productid
WHERE op.orderid=1016186
AND plo.stockid=903958

但它给了我以下结果:

+---------+-----------+---------+-----------+------------+
| orderid | productid | stockid | order_qty | stock_qty  |
+---------+-----------+---------+-----------+------------+
| 1016186 |    265385 |  903958 |         5 |       200  |
| 1016186 |    407454 |  903958 |         6 |       205  |
| 1016186 |    975045 |  903958 |         7 |       210  |
+---------+-----------+---------+-----------+------------+

我不知道我错过了什么。

Answer 1

WHERE 子句中的以下条件是导致结果集中丢失记录的原因:

WHERE plo.stockid = 903958

orderedproducts 表中缺失的记录与 productsinstocks 表中的任何记录都不匹配。因此，此记录的 stockid 值为 NULL，您当前的 WHERE 条件会将其过滤掉。

相反，将检查 OR plo.stockid IS NULL 添加到 WHERE 子句以保留此记录:

SELECT op.orderid, op.productid, plo.stockid, op.quantity order_qty,
       plo.quantity AS stock_qty
FROM orderedproducts op
LEFT JOIN productsinstocks plo
    ON op.productid = plo.productid
WHERE op.orderid = 1016186 AND
      (plo.stockid = 903958 OR plo.stockid IS NULL)

此处演示:

SQLFiddle