SQL-Join with NULL-columns

Question

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

Solution 1

No, that's pretty much it.

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

Solution 2

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

Solution 3

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)

Comments

[tstenner]

b.bid and b.cid can't be Null, so the extra test is unnecessary, but I like the second example without distinct.

[Thomas]

@tstenner - Ah. Missed that you stated the columns in b as being not-nullable.