SQL-Join with NULL-columns

I'm having the following tables:

Table a

Table b

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)