mysql - 当JOIN表为空时如何显示数据库中的数据？

Question

自从我开始自学以来，这个问题就一直困扰着我。假设我有这个查询:

SELECT * FROM ve_articles a 
JOIN ve_articles_categories c ON a.CategoryId=c.id 
JOIN ve_articles_comments ac ON a.id=ac.ArticleId WHERE a.UserId='$id'

此查询将返回空结果集，因为给定文章没有评论。但有一篇文章。那么，即使ve_articles_comments中没有数据，我如何仍然将第三个表ve_articles_comments与ve_articles连接并显示文章？

Answer 1

您可以使用LEFT JOIN代替JOIN。 Here's MySQL的文档是这样说的:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table.

尝试以下查询:

SELECT * FROM ve_articles a 
LEFT JOIN ve_articles_categories c ON a.CategoryId=c.id 
LEFT JOIN ve_articles_comments ac ON a.id=ac.ArticleId 
WHERE a.UserId='$id';