title: How to do left outer join in spark sql?
body: <p>I am trying to do a left outer join in spark (1.6.2) and it doesn't work. My sql query is like this:</p>
  
  <pre><code>sqlContext.sql("select t.type, t.uuid, p.uuid
  from symptom_type t LEFT JOIN plugin p 
  ON t.uuid = p.uuid 
  where t.created_year = 2016 
  and p.created_year = 2016").show()
  </code></pre>
  
  <p>The result is like this:</p>
  
  <pre><code>+--------------------+--------------------+--------------------+
  |                type|                uuid|                uuid|
  +--------------------+--------------------+--------------------+
  |              tained|89759dcc-50c0-490...|89759dcc-50c0-490...|
  |             swapper|740cd0d4-53ee-438...|740cd0d4-53ee-438...|
  </code></pre>
  
  <p>I got same result either using LEFT JOIN or LEFT OUTER JOIN (the second uuid is not null).</p>
  
  <p>I would expect the second uuid column to be null only. how to do a left outer join correctly?</p>
  
  <p>=== Additional information ==</p>
  
  <p>If I using dataframe to do left outer join i got correct result.</p>
  
  <pre><code>s = sqlCtx.sql('select * from symptom_type where created_year = 2016')
  p = sqlCtx.sql('select * from plugin where created_year = 2016')
  
  s.join(p, s.uuid == p.uuid, 'left_outer')
  .select(s.type, s.uuid.alias('s_uuid'), 
          p.uuid.alias('p_uuid'), s.created_date, p.created_year, p.created_month).show()
  </code></pre>
  
  <p>I got result like this:</p>
  
  <pre><code>+-------------------+--------------------+-----------------+--------------------+------------+-------------+
  |               type|              s_uuid|           p_uuid|        created_date|created_year|created_month|
  +-------------------+--------------------+-----------------+--------------------+------------+-------------+
  |             tained|6d688688-96a4-341...|             null|2016-01-28 00:27:...|        null|         null|
  |             tained|6d688688-96a4-341...|             null|2016-01-28 00:27:...|        null|         null|
  |             tained|6d688688-96a4-341...|             null|2016-01-28 00:27:...|        null|         null|
  </code></pre>
  
  <p>Thanks,</p>