title: How to INNER JOIN on non-empty values BUT AT THE SAME TIME LEFT JOIN on empty values?
body: <p>It seems that I need a <code>MAGIC JOIN</code> that should work as <code>INNER JOIN</code> on non-empty values but at the same time work as <code>LEFT JOIN</code> on empty values. By "empty" I mean a non-null zero-length <code>char</code> value, like this: <code>''</code>.</p>
  
  <p>I have table <code>Customers</code>:</p>
  
  <pre><code>CustomerCode    Customer_AddressCode
  CC01            AC01
  CC02            somebullcrap
  CC03
  CC04            AC02
  CC05
  </code></pre>
  
  <p>and <code>Addresses</code>:</p>
  
  <pre><code>AddressCode    AddressValue
  AC01           City 01
  AC02           City 02
  AC03           City 03
  AC04           City 04
  AC05           City 05
  </code></pre>
  
  <p>The desired output is like this:</p>
  
  <pre><code>CustomerCode  AddressValue
  CC01          City 01
  CC03          NULL
  CC04          City 04
  CC05          NULL
  </code></pre>
  
  <p>so it would omit address with code <code>somebullcrap</code> that does not exist in <code>Addresses</code> table.</p>
  
  <p>The best I could think of is this:</p>
  
  <pre><code>SELECT c.CustomerCode, a.AddressValue
  FROM Customers c
  LEFT JOIN Addresses a ON a.AddressCode = c.Customer_AddressCode
  WHERE c.Customer_AddressCode = ''
  OR c.Customer_AddressCode IN
  (
      SELECT AddressCode
      FROM Address
  )
  </code></pre>
  
  <p>Is there any more elegant solution to this?</p>
  
  <p><strong>And yes, I do know about nullable foreign keys.</strong> I'm just asking about solution without altering tables / making temp tables / modifying existing table data.</p>