显示找不到键的查询结果[MySQL] [英] Show query results for keys not found [MySQL]

问题描述

如果我正在比较2个表users [user_id, username]和user_comments [user_id, comment]，其中user_id是两个表中的键.如果我创建一个简单的查询来查找所有发表评论的用户的用户名.是否有可能在user_comments中有一个user_id没有与users链接到user_id的问题，我们是否可以在查询中向默认user_id给出anonymous的结果生成默认响应?

IF I am comparing 2 tables users [user_id, username] and user_comments [user_id, comment] where user_id are the keys in both tables. If I create a simple query to find the usernames for all users that made a comment. Is it possible if there is a user_id within user_comments that doesn't link to a user_id with users can we generate default response within the query where the unknown user_id is given a result of anonymous?

基本上我有5个user_id，在users和user_comments中都可以找到4个，但是最后一个user_id中可以找到.我可以创建一个查询来显示2列之间的5个结果，但是对于未知的user_id，它给出的结果是unknown?

Basically I have 5 user_id 4 of which I can find in both users and user_comments however the last user_id. Can I create a query that will show the 5 results between the 2 columns however for the unknown user_id it gives a result of unknown?

推荐答案

我认为您想要一个left join:

select coalesce(cast(uc.user_id as varchar(255)), 'unknown') as user_id,
       uc.comment
from user_comments uc left join
     users u
     on u.user_id = uc.user_id;

您确实需要注意user_id的类型.假设它不是字符串，则需要将其强制转换为与'unknown'兼容.否则，NULL是一个很好的不匹配值.

You do need to be careful about the type of user_id. Assuming it is not a string, you need to cast it to be compatible with 'unknown'. Otherwise, NULL is a fine non-matching value.

这篇关于显示找不到键的查询结果[MySQL]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！