title: Inner Join on multiple id's if they are not null
body: <p>I've been having some trouble creating a join that captures the following records. I have spent about 5 hours yesterday trying to figure it out, but could not. </p>
  
  <p>I have two tables, <strong>Table A</strong> and <strong>Table B</strong>
  Both these tables have the following columns:</p>
  
  <pre><code>ID_1, ID_2, ID_3, ID_4 
  </code></pre>
  
  <p>Now, I need to create a join between both tables so that the result pulls records on matching IDs that are not null, and if more than 1 ID matches, then I use all IDs that match to pull up the record, so there are a couple scenarios: </p>
  
  <p>Scenario 1: All IDs in both tables match perfectly (this is easy to code)</p>
  
  <p><strong>Here I would join by All ID'S.</strong></p>
  
  <pre><code>+--------+---------+---------+--------+
  | A.ID_1 |  A.ID_2 |  A.ID_3 | A.ID_4 |
  +--------+---------+---------+--------+
  | CAD    |   AAPL  |     853 |    200 |
  +--------+---------+---------+--------+
  
  +--------+--------+--------+--------+
  | B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
  +--------+--------+--------+--------+
  | CAD    | AAPL   |    853 |    200 |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p>Scenario 2: One or more  ID matches in both tables, the rest are NULL (also easy) </p>
  
  <p><strong>Here I would join by ID_1 and ID_3 only.</strong> </p>
  
  <pre><code>+--------+--------+--------+--------+
  | A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
  +--------+--------+--------+--------+
  | CAD    | NULL   |    933 | NULL   |
  +--------+--------+--------+--------+
  
  +--------+--------+--------+--------+
  | B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
  +--------+--------+--------+--------+
  | CAD    | NULL   |    933 | NULL   |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p>Scenario 3: One or more ID's match in the tables but some don't </p>
  
  <p><strong>Here I need to join on only ID_1 and ID_2, as ID_3 and ID_4 are NULL for respective tables.</strong></p>
  
  <pre><code>+--------+--------+--------+--------+
  | A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
  +--------+--------+--------+--------+
  | CAD    |  TSLA  |    341 | NULL   |
  +--------+--------+--------+--------+
  
  +--------+--------+--------+--------+
  | B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
  +--------+--------+--------+--------+
  | CAD    |  TSLA  |  NULL  |    250 |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p>Scenario 4: All ID's have NULL, therefore record is rejected </p>
  
  <p>So if 
  <strong>Table A contained the following:</strong> </p>
  
  <pre><code>+--------+--------+--------+--------+
  | A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
  +--------+--------+--------+--------+
  | CAD    |  AAPL  |  853   |   200  |
  +--------+--------+--------+--------+
  | CAD    | NULL   |  933   | NULL   |
  +--------+--------+--------+--------+ 
  | CAD    |  TSLA  |  341   | NULL   |
  +--------+--------+--------+--------+
  | NULL   |  NULL  |  NULL  | NULL   |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p><strong>Table B contained the following:</strong> </p>
  
  <pre><code>+--------+--------+--------+--------+
  | B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
  +--------+--------+--------+--------+
  | CAD    |  AAPL  |  853   |   200  |
  +--------+--------+--------+--------+
  | CAD    |  NULL  |  933   |  NULL  |
  +--------+--------+--------+--------+ 
  | CAD    |  TSLA  |  NULL  |   250  |
  +--------+--------+--------+--------+
  | NULL   |  NULL  |  NULL  |  NULL  |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p><strong>Result would be:</strong> </p>
  
  <pre><code>+--------+--------+--------+--------+
  | ID_1   | ID_2   | ID_3   | ID_4   |
  +--------+--------+--------+--------+
  | CAD    |  AAPL  |  853   |   200  |
  +--------+--------+--------+--------+
  | CAD    |  NULL  |  933   |  NULL  |
  +--------+--------+--------+--------+ 
  | CAD    |  TSLA  |  341   |  NULL  |
  +--------+--------+--------+--------+
  </code></pre>
  
  <p>Thanks</p>