SQL-Join with NULL-columns
- by tstenner
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?
