Mysql 左连接空结果

发布于 2024-09-02 06:28:01 字数 324 浏览 8 评论 0原文

我有这个查询

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。无论如何，如果它为空，它会返回类似“用户未找到”的内容吗？或者我必须使用 php 来做到这一点？

原文

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 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如果没结果 2024-09-09 06:28:01

使用：

   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

文档：

COALESCE

选择的理由COALESCE 优于 IF 或 IFNULL 的原因是 COALESCE 是 ANSI 标准，而其他方法在其他数据库上无法可靠实现。在查看 IF 之前，我会使用 CASE，因为 CASE 是 ANSI 标准，可以更轻松地将查询移植到其他数据库。

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:

COALESCE

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.

回复收藏 0 原文

野侃 2024-09-09 06:28:01

您可以使用 IFNULL 函数：

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

You can use the IFNULL function:

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

回复收藏 0 原文

听风吹 2024-09-09 06:28:01


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


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

回复收藏 0 原文

爱本泡沫多脆弱 2024-09-09 06:28:01

您可以在 Oracle 中使用解码的地方使用 IF()。

所以

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

应该有效。
注意：我手边没有mysql，所以没有测试查询。但如果失败，应该进行较小的修改。不要投反对票；）

You can use IF() where in Oracle you would have used decode.

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 ;)

回复收藏 0 原文

~没有更多了~