title: Inner or Outer joins with Pivot query
body: <p>I have a query that pivots a rows into columns.  The rows only exist if values are present, e.g. there are no rows with the values represented by NULL</p>
  
  <pre><code>Object Field_id    value
  1      1           value1
  1      2           value2
  1      3           value3
  2      2           value4
  </code></pre>
  
  <p>And I create the output</p>
  
  <pre><code>Object  field1  field2  field3
  1       value1  value2  value3
  2               value4
  </code></pre>
  
  <p>using a query like</p>
  
  <pre><code>select * from 
      (
          select fs.field_name,  s.text_value, s.id_object 
            from custom_field_str s  , 
          (select  ad.id_field field_id, fd.name field_name, 
  num_display_order display_order, ad.text_table_name catalogue_table, ad.num_lines 
             from catalogues c 
            inner join attribute_definitions ad on c.id_object = ad.id_object_type 
            inner join field_definitions fd on ad.id_field = fd.id_object 
            where c.id_object = 'cA1') fs 
          where fs.catalogue_table = 'custom_field_str' 
            and fs.field_id = s.id_field 
      )
  pivot 
      ( max(text_value)  
  for field_name IN ('field1' as  field1,'field2' as  field2,'field3' as  field3)  )
  </code></pre>
  
  <p>So my question is, should I be using an outer join in the join between custom_field_str and the derived table fs.  Or does the PIVOT not require a full set of results to build the output grid?</p>