mysql外部连接

mysql外部连接 - 确定连接的行是否存在(mysql outer join - determine if the joined row exists)

我有两个主键相同的表，但其中一个比另一个大得多。 我想知道哪个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;

最满意答案

这与您显示的内容具有相同的逻辑，但代码较短：

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;