tstenner tstenner - 10 months ago 34
SQL Question

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

Answer Source

No, that's pretty much it.

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download