mysql outer join - determine if the joined row exists

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;

---

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;

---

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.

CASE WHEN b.id IS NULL THEN ...