mysql 外连接 - 确定连接的行是否存在

发布于 2024-11-27 04:43:52 字数 342 浏览 1 评论 0 原文

我有两个具有相同主键的表，但一个比另一个大得多。我想知道哪些 id 在较小的表中有一行。（在示例中，a 较大，b 较小）。现在，我使用带有 CASE 的 OUTER JOIN 来确定 b 值是否为 NULL。它不起作用（总是得到 1）。解决这个问题固然很好，但必须有更好的方法。我该怎么做呢？

SELECT a.id,
       CASE b.id
         WHEN NULL THEN 0
         ELSE 1
         END AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

原文

I have two tables with the same primary key, but one is much much larger than the other. I want to know which ids have a row in the smaller table. (In the example, a is large and b is small). Right now, I'm using an OUTER JOIN with a CASE to determine if the b value is NULL or not. It's not working (always getting 1). Fixing this would be fine, but there's got to be a better way. How should I do it?

SELECT a.id,
       CASE b.id
         WHEN NULL THEN 0
         ELSE 1
         END AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

分享到QQ

分享到微博

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

发布评论

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

你的往事 2024-12-04 04:43:52

这与您所展示的逻辑相同，但代码更短：

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

this has the same logic of what you showed but has a shorter code:

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

回复收藏 0 原文

纸伞微斜 2024-12-04 04:43:52

不。检查外键列中的 NULL 正是您执行此操作的方法。

但是，没有任何东西等于 NULL （它不是一个值），这就是为什么您的 CASE 转到 ELSE 部分。您需要使用IS NULL来检查列是否为NULL。

CASE WHEN b.id IS NULL THEN ...

No. Checking for a NULL in the foreign key column(s) is exactly how you do this.

However, nothing is ever equal to NULL (it's not a value), which is why your CASE goes to the ELSE portion. You need to use IS NULL to check if a column is NULL.