mysql - SQL LEFT JOIN/INNER JOIN 不同的结果

Question

我不知道我做错了什么，但找不到我的陈述的解决方案。目前的说法是:

SELECT tb1.RestaurantID, Title, City
FROM Restaurant AS tb1
LEFT JOIN RestaurantLunch AS tb2 ON tb1.RestaurantID = tb2.RestaurantID
WHERE (MATCH (tb1.Title, tb1.City) AGAINST ('+string1* +string2*' IN BOOLEAN MODE)) LIMIT 5;

当然我也想在其他表(tb2)中搜索字符串，我将我的语句更改为:

SELECT tb1.RestaurantID, Title, City
FROM Restaurant AS tb1
LEFT JOIN RestaurantLunch AS tb2 ON tb1.RestaurantID = tb2.RestaurantID
WHERE (MATCH (tb1.Title, tb1.City, tb2.Text) AGAINST ('+string1* +string2*' IN BOOLEAN MODE)) LIMIT 5;

提交后我得到不同的结果，这根本不符合逻辑。这是为什么？

Answer 1

当您执行左联接并在右侧表的 where 部分中创建条件(在您的情况下为 tb2)时，此左联接成为具有该条件的普通联接。这就是为什么你会得到不同的结果。

试试这个方法:

SELECT tb1.RestaurantID, Title, City
  FROM Restaurant AS tb1
        LEFT JOIN RestaurantLunch AS tb2 
                  ON tb1.RestaurantID = tb2.RestaurantID
                     AND (MATCH (tb2.Text) 
                              AGAINST ('+string1* +string2*' IN BOOLEAN MODE))
 WHERE (MATCH (tb1.Title, tb1.City) 
            AGAINST ('+string1* +string2*' IN BOOLEAN MODE)) LIMIT 5;