SQL-Join with NULL-columns

I'm having the following tables:

Table a

Field	Type	Null	Key
bid	int(10) unsigned	YES
cid	int(10) unsigned	YES

Table b

Field	Type	Null
bid	int(10) unsigned	NO
cid	int(10) unsigned	NO
data	int(10) unsigned	NO

When I want to select all rows from b where there's a corresponding bid/cid-pair in a, I simply use a natural join SELECT b.* FROM b NATURAL JOIN a; and everything is fine.

When a.bid or a.cid is NULL, I want to get every 开发者_C百科row where the other column matches, e.g. if a.bid is NULL, I want every row where a.cid = b.cid, if both are NULL I want every column from b.

My naive solution was this:

SELECT DISTINCT b.*
FROM b
JOIN a ON (ISNULL(a.bid) OR a.bid=b.bid ) AND (ISNULL(a.cid) OR a.cid=b.cid)

Is there any better way to to this?

---

The ISNULL function is not actually ANSI compliant. Yes, you do need to check for nulls in both columns. Another way to write your query would be:

Select Distinct b.*
From b
    Join a
        On ( a.bid = b.bid Or ( a.bid Is Null And b.bid Is Null ) )
            And ( a.cid = b.cid Or ( a.cid Is Null And b.cid Is Null ) )

Yet another way that avoids the use of Distinct:

Select b.*
From b
Where Exists    (
                Select 1
                From a
                Where  ( a.bid = b.bid Or ( a.bid Is Null And b.bid Is Null ) )
                    And ( a.cid = b.cid Or ( a.cid Is Null And b.cid Is Null ) )
                )

---

No, that's pretty much it.

(I'd generally rephrase ISNULL(a.bind) as a.bind IS NULL for ANSI SQL compliance FWIW.)

---

Too old, but here is my 2 cents, it might be useful for someone:

ISNULL(a.cid, 0) = ISNULL(b.cid) AND ISNULL(a.bid, 0) = ISNULL(b.bid)