title: Transact SQL - which Join to Use
body: <p>I have two simple SELECT statements:
  The first shows a list of Features.</p>
  
  <pre><code>SELECT * FROM Features
  
  id  name
  --  ----
  1   24 Hour Access
  2   24 hour CCTV monitoring
  3   Airport location
  4   Break-Out Areas
  5   Business Lounge
  6   Business park location
  snip..
  </code></pre>
  
  <p>and the second statement shows a list of feature information that has changed</p>
  
  <pre><code>SELECT 
      *
  FROM 
      #SmartFeaturesToUpdate new_features
  ORDER BY 
      new_features.centre_translation_id,
      new_features.feature_id,
      new_features.feature_selected   
  
  feature_id  centre_translation_id feature_selected
  ----------  --------------------- ----------------
  1           1                 1
  2           1                 1
  5           1                 1
  10          1                 1
  11          1                 1
  snip..
  </code></pre>
  
  <p>What I want to see is all of the features by centre translation.
  Combining the tables gives me:</p>
  
  <pre><code>SELECT 
      *
  FROM 
      #SmartFeaturesToUpdate new_features
      LEFT JOIN Feature feature ON feature.id = new_features.feature_id
  ORDER BY 
      new_features.centre_translation_id,
      new_features.feature_id,
      new_features.feature_selected
  
  feature_id  centre_translation_id feature_selected id name
  ----------  --------------------- ---------------- -- ----
  1           1                 1                1  24 Hour Access
  2           1                 1                2  24 hour CCTV monitoring
  5           1                 1                5  Business Lounge
  10          1                 1                10 Double Glazing
  11          1                 1                11 Elevator
  snip..
  </code></pre>
  
  <p>The result above is missing feature id's 3 and 4, because they are not in the second list.
  but the result I need is:</p>
  
  <pre><code>feature_id  centre_translation_id feature_selected id name
  ----------  --------------------- ---------------- -- ----
  1           1                 1                1  24 Hour Access
  2           1                 1                2  24 hour CCTV monitoring
  3           1                 1                3  Airport Location
  4           1                 1                4  Break-Out Area
  5           1                 1                5  Business Lounge
  snip..
  </code></pre>
  
  <p>How should I modify the third SELECT statement to acheive this and combine the results from both the features and feature information list?</p>