title: SQL-Join with NULL-columns
body: <p>I'm having the following tables:</p>
  
  <pre>Table a
  +-------+------------------+------+-----+
  | Field | Type             | Null | Key |
  +-------+------------------+------+-----+
  | bid   | int(10) unsigned | YES  |     |
  | cid   | int(10) unsigned | YES  |     |
  +-------+------------------+------+-----+</pre>
  
  <pre>Table b
  +-------+------------------+------+
  | Field | Type             | Null |
  +-------+------------------+------+
  | bid   | int(10) unsigned | NO   |
  | cid   | int(10) unsigned | NO   |
  | data  | int(10) unsigned | NO   |
  +-------+------------------+------+</pre>
  
  <p>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 <code>SELECT b.* FROM b NATURAL JOIN a;</code> and everything is fine.</p>
  
  <p>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 <code>a.cid=b.cid</code>, if both are NULL I want every column from b.</p>
  
  <p>My naive solution was this:</p>
  
  <pre><code>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 )</code></pre>
  
  <p>Is there any better way to to this?</p>