Mysql Left Join空结果 [英] Mysql Left Join Null Result

问题描述

我有这个查询

SELECT articles.*, 
       users.username AS `user` 
 FROM `articles` 
LEFT JOIN `users` ON articles.user_id = users.id 
 ORDER BY articles.timestamp 

基本上，它返回文章列表和与之关联的用户名.现在，如果在用户表中没有用于特定用户ID的条目，则users var为NULL.无论如何，如果它为null，它会返回类似"User Not Found"的信息?还是我必须使用php来做到这一点?

Basically it returns the list of articles and the username that the article is associated to. Now if there is no entry in the users table for a particular user id, the users var is NULL. Is there anyway to make it that if its null it returns something like "User Not Found"? or would i have to do this using php?

推荐答案

使用:

   SELECT a.*, 
          COALESCE(u.username, 'User Not Found') AS `user` 
     FROM ARTICLES a
LEFT JOIN USERS u ON u.id = a.user_id
 ORDER BY articles.timestamp 

文档:

• 凉粉

之所以选择IF或IFNULL而不选择COALESCE的原因是，COALESCE是ANSI标准，而其他方法不能在其他数据库上可靠地实现.在考虑IF之前，我会先使用CASE，因为这又是因为-CASE是ANSI标准，因此可以更轻松地将查询移植到其他数据库.

The reason to choose COALESCE over IF or IFNULL is that COALESCE is ANSI standard, while the other methods are not reliably implemented over other databases. I would use CASE before I'd look at IF because again - CASE is ANSI standard, making it easier to port the query to other databases.

这篇关于Mysql Left Join空结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！