mysql - 即使在第二个表中找不到匹配项，也从第一个表中获取结果 - MySQL

Question

我有两个表，其结构如下

Table name = counter
ref_id INT NOT NULL
count INT NOT NULL

---

Table name = favs
disqus_id VARCHAR(32) NOT NULL
user_id INT NOT NULL
dormant VAHCHAR(10) NOT NULL

我运行此查询 => SELECT count, dormant FROM counter AS c LEFT JOIN favs AS f ON c.ref_id = f.disqus_id WHERE ref_id = 'post_5' AND user_id = '1'。

但如果在第二个表 favs 中未找到匹配项，它不会返回任何行。我想要的是，如果找到结果，它应该返回两列，否则返回两列，第二列为 NULL

我该怎么做？

Answer 1

用户在 ON 子句上的条件

SELECT c.count, f.dormant 
FROM   counter AS c 
       LEFT JOIN favs AS f 
          ON c.ref_id = f.disqus_id  AND f.user_id = '1'
WHERE  c.ref_id = 'post_5' 

或者如果还是不行，移动两个:

SELECT c.count, f.dormant 
FROM   counter AS c 
       LEFT JOIN favs AS f 
          ON c.ref_id = f.disqus_id  AND 
             f.user_id = '1' AND
             c.ref_id = 'post_5'