mysql - 在查询中显示没有值的项目

Question

我写了以下查询:

select title, branch_name, no_of_copies 
from Book_Copies BC 
inner join Book B on BC.book_id = B.book_id 
inner join Library_Branch LB on BC.branch_id = LB.branch_id 
where title = 'Grapes of Wrath';

但我的问题是，这仅显示具有 > 0 no_of_copies 的标题和分支名称。如何显示没有 no_of_copies 行的副本？ (它等于 0)。

这是我目前得到的表格:

+-----------------+-------------+--------------+
| title           | branch_name | no_of_copies |
+-----------------+-------------+--------------+
| Grapes of Wrath | Central     |            5 |
| Grapes of Wrath | Allentown   |            2 |
+-----------------+-------------+--------------+

这是我需要的:

+-----------------+-------------+--------------+
| title           | branch_name | no_of_copies |
+-----------------+-------------+--------------+
| Grapes of Wrath | Central     |            5 |
| Grapes of Wrath | Allentown   |            2 |
| Grapes of Wrath | Sharpstown  |            0 |
+-----------------+-------------+--------------+

Answer 1

您需要使用外连接，而不是从book 表开始。也许是这样的:

select title, branch_name, no_of_copies 
from Book B
    left join Book_Copies BC on BC.book_id = B.book_id 
    left join Library_Branch LB on BC.branch_id = LB.branch_id 
where title = 'Grapes of Wrath';

---

阅读您的评论，听起来您想在 book 和 library_branch 之间使用cross join 来获取书籍和分支机构的所有组合(也称为 笛卡尔积)。然后 outer join 将这些结果添加到查找表中:

select title, branch_name, coalesce(no_of_copies,0) copies
from Book B 
    cross join Library_Branch LB
    left join Book_Copies BC on BC.book_id = B.book_id 
                            and BC.branch_id = LB.branch_id 
where title = 'Grapes of Wrath';