mysql outer join - determine if the joined row exists

Question

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;

score 7 · Accepted Answer · answered Jul 29 '11 at 21:17

7

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;

answered Jul 29 '11 at 21:17

Dalen

8,856
4
47
52

score 3 · Answer 2 · answered Jul 29 '11 at 21:13

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

mysql outer join - determine if the joined row exists

2 Answers2