title: RIGHT OUTER JOIN and WHERE when there are matching rows
body: <p>I am having issues understanding the RIGHT OUTER JOIN logic.</p>
  
  <p>We have two tables of data - <code>Process</code> and <code>Resource</code></p>
  
  <pre><code>Process                             Resource    
  ProcessID   ResourceID  Date        ResourceID  ResourceName
  1           1           01/01/2015  1           Resource 1
  2           1           02/01/2015  2           Resource 2
  3           2           02/01/2015          
  </code></pre>
  
  <p>We created the SELECT statement </p>
  
  <pre><code>SELECT *
  FROM Process P
  RIGHT OUTER JOIN Resource R ON P.ResourceID = R.ResourceID
  WHERE P.Date = '2015-01-01'
  </code></pre>
  
  <p>And the expected results are</p>
  
  <pre><code>ProcessID   ResourceID  Date        ResourceID  ResourceName    
  1           1           01/01/2015  1           Resource 1  
  NULL        NULL        NULL        2           Resource 2  &lt;-- This row is not returned
  </code></pre>
  
  <p>However we only receive one row - we want each resource for each process that we select, with null values returned from the process table where they dont match.</p>
  
  <p>If we change the query and remove the row with ProcessID of 3 the query returns both resources, but our example has a join on a row we have not selected, so seems not to be returned.</p>
  
  <pre><code>SELECT *
  FROM Process P
  RIGHT OUTER JOIN Resource R ON P.ResourceID = R.ResourceID
  WHERE P.Date = '2015-01-01' OR P.Date IS NULL
  </code></pre>
  
  <p>How can we make the query return the expected results with the data shown above?  What are we missing?</p>