title: MySQL: Using NULL as wildcard in JOIN statement
body: <p>I have a table containing perfectly defined items and a second table with potentially vague/greedy orders as NULL would require all available values for this parameter.</p>
  
  <pre><code>items
  +-----------------------+
  | item_id | color | size |
  |---------+-------+------|
  |    1    |  blue |    8 |
  |    2    |   red |    6 |
  |    3    | green |    7 |
  |    4    | black |    6 |
  +------------------------+
  
  orders
  +-------------------------+
  | order_id | color | size |
  |----------+-------+------|
  |     1    |   red |    6 |
  |     2    | green |    8 |
  |     3    |  NULL |    6 |
  |     4    |  blue | NULL |
  |     5    |  NULL | NULL |
  +-------------------------+
  </code></pre>
  
  <p>Is there an efficient way to generate a complete list of items needed to fill all orders?</p>
  
  <pre><code>+--------------------+
  | order_id | item_id |
  |----------+---------|
  |     1    |    2    |
  |     3    |    2    |
  |     3    |    4    |
  |     4    |    1    |
  |     5    |    1    |
  |     5    |    2    |
  |     5    |    3    |
  |     5    |    4    |
  +--------------------+
  </code></pre>
  
  <p>It seems to me like an INNER JOIN should be able to do this, but something like this obviously doesn't consider the possibility of NULL values as greedy wildcards in the <em>orders</em> table:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT order_id, item_id
  FROM orders
  INNER JOIN items ON orders.color = items.color AND orders.size = items.size
  </code></pre>
  
  <p>Any ideas?</p>