mysql LEFT JOIN 与来自其他表的条件

Question

我有两个 mysql 表:

table-1: table_item

╔════╦═══════╦════════╦══════════╗
║ id ║ catid ║ itemid ║ itemname ║
╠════╬═══════╬════════╬══════════╣
║  1 ║   1   ║   1    ║ Pen      ║
║  2 ║   1   ║   2    ║ Pencil   ║
║  3 ║   1   ║   3    ║ Sharpner ║
║  4 ║   2   ║   4    ║ Book     ║
║  5 ║   2   ║   5    ║ Notebook ║
║  6 ║   3   ║   6    ║ Pant     ║
║  7 ║   4   ║   7    ║ Shirt    ║
╚════╩═══════╩════════╩══════════╝

表 2:bid-2015

╔════╦════════╦══════╦══════╗
║ id ║ itemid ║ year ║ rate ║
╠════╬════════╬══════╬══════╣
║  1 ║   1    ║ 2015 ║ 3.0  ║
║  2 ║   2    ║ 2015 ║ 5.0  ║
║  3 ║   5    ║ 2015 ║ 7.0  ║
║  4 ║   1    ║ 2016 ║ 3.5  ║
║  5 ║   5    ║ 2016 ║ 7.8  ║
║  6 ║   7    ║ 2016 ║ 20.0 ║
╚════╩════════╩══════╩══════╝

我想要如下结果:

结果表:2015 年

╔══════════╦══════╦══════╗
║ itemname ║ rate ║ year ║
╠══════════╬══════╬══════╣
║ Pen      ║ 3.0  ║ 2015 ║
║ Pencil   ║ 5.0  ║ 2015 ║
║ Sharpner ║ null ║ null ║
╚══════════╩══════╩══════╝

如果我运行下面的查询得到上面的结果表:

SELECT i.itemname, b.rate, b.year
FROM table_item i LEFT JOIN bid-2015 b ON i.itemid=b.itemid
WHERE i.catid=1 AND b.year=2015

然后它给了我以下输出:

╔══════════╦══════╦══════╗
║ itemname ║ rate ║ year ║
╠══════════╬══════╬══════╣
║ Pen      ║ 3.0  ║ 2015 ║
║ Pencil   ║ 5.0  ║ 2015 ║
║ Notebook ║ 7.0  ║ 2015 ║
╚══════════╩══════╩══════╝

我怎样才能 LEFT JOIN 那些带条件的表，以便它可以输出第一个表？

Answer 1

LEFT JOIN 类似于 INNER JOIN，不同之处在于它将至少返回 a 中的每条记录一次，如果没有实际匹配的记录，则用 NULL 值替换 b 中缺失的字段。

但是，WHERE 条件是在 LEFT JOIN 之后计算的，因此上面的查询会在列被连接后检查它。没有NULL值可以满足相等条件，所以a中没有b中相应记录的记录将不可避免地被过滤掉。

本质上，这个查询是一个 INNER JOIN，只是效率较低。

为了只匹配 b.column = 'something' 的记录(同时仍然从 a 返回所有记录)，这个条件应该移到 ON 子句中:

SELECT i.itemname,b.rate,b.year 
from table_item i 
LEFT JOIN bid-2015 b 
ON i.itemid=b.itemid
AND i.catid=1 AND b.year=2015