在 SQL 中连接两个表并返回包含空值的结果集

问题描述

我有投票站点，其中每个站点都是表格中的一行.现在在我的网络服务器上，我需要加载所有站点，并检查每个站点是否被投票(投票意味着站点 ID 存在于其他表的行中).

I have voting sites where each site is a row in the table. Now on my web server, I need to load all sites, and check for each site if its been voted (Voted means the site id exists in the other tables's row).

因此，如果站点 id = 5 并且 site_id 5 的行存在于 callback_votes 中，那么查询会将该 id 添加到 'voted'，否则为空.

So if the site id = 5 and row with site_id 5 exists in callback_votes, then the query will add that id to 'voted', if not it will be null.

示例:

SELECT sites.*, callback_votes.site_id AS voted
FROM sites
INNER JOIN callback_votes ON callback_votes.site_id = sites.id;

此查询有效，但是，如果 callback_votes 中没有任何行，查询将不返回任何数据.我想要做什么，我仍然想返回 sites.*，只是为了 voted 在这种情况下为 null.

This query works, however, if I will not have any rows in callback_votes, the query will return no data. What I want to do, I want to still return sites.*, just for voted to be null in that case.

这是可能的还是有其他方法?

Is that possible or are there other ways for this?

推荐答案

您正在使用 INNER JOIN 它将返回每个表中有匹配行的行，您需要的是 .

You're using INNER JOIN which will return rows where there is a matching row in each table, what you need is a LEFT JOIN.

使用 LEFT JOIN 简单来说意味着从左表中选择所有行，如果右表中没有匹配的行，则返回 null".

Using LEFT JOIN in simplistic terms means "select all rows from the left table, where there are no matching rows in the right table then return null".

您的查询可能如下所示:

Here's how your query may look:

SELECT sites.*, callback_votes.site_id AS voted
FROM sites
LEFT JOIN callback_votes ON callback_votes.site_id = sites.id;

这篇关于在 SQL 中连接两个表并返回包含空值的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！