MySQL Mysql左加入空结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2894075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql Left Join Null Result
提问by Ozzy
I have this query
我有这个查询
SELECT articles.*,
users.username AS `user`
FROM `articles`
LEFT JOIN `users` ON articles.user_id = users.id
ORDER BY articles.timestamp
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 usersvar 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?
基本上它返回文章列表和文章关联的用户名。现在，如果用户表中没有特定用户 ID 的条目，则usersvar 为 NULL。无论如何，如果它为空，它会返回“找不到用户”之类的东西吗？还是我必须使用 php 来做到这一点？
回答by OMG Ponies
Use:
用：
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
Documentation:
文档：
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.
选择 COALESCE 而不是 IF 或 IFNULL 的原因是 COALESCE 是 ANSI 标准，而其他方法在其他数据库上无法可靠实现。我会在查看 IF 之前使用 CASE，因为再次 - CASE 是 ANSI 标准，可以更轻松地将查询移植到其他数据库。
回答by Sean Reilly
回答by ring bearer
You can use IF()where in Oracle you would have used decode.
您可以IF()在 Oracle 中使用解码的位置。
So
所以
SELECT articles.*, IF(users.username IS NULL, 'No user found', users.username) AS `user`
FROM `articles` LEFT JOIN `users` ON articles.user_id = users.id
ORDER BY articles.timestamp
Should work. Note: I dont have mysql handy, so did not test the query. But should work with minor modifications if it fails. Do not downvote ;)
应该管用。注意：我手边没有mysql，所以没有测试查询。但如果失败，应该进行小的修改。不要投反对票；)
回答by Christian Neverdal
SELECT articles.*,
IFNULL(users.username,'User Not Found') AS `user`
FROM `articles`
LEFT JOIN `users` ON articles.user_id = users.id
ORDER BY articles.timestamp
