title: how to use union resolve this full outer join problem under mySQL
body: <p>Here is the table</p>
  
  <pre><code> stuid stuname subject grade
   1     alex    algo    99
   1     alex    dastr   100
   2     bob     algo    90
   2     bob     dastr   95
   3     casy    algo    100
   4     Daisy   dastr   100
  </code></pre>
  
  <p>case1: assuming there are only two subjects in the table
  Following is the expected output</p>
  
  <pre><code> stuname algo dastr 
   alex    99    100
   bob     90    95
   casy    100   0
   Daisy   0     100
  </code></pre>
  
  <p>I think following is a workable query</p>
  
  <pre><code>select g1.stuname,
         COALESCE(g1.grade,0) as algo
         COALESCE(g2.grade,0) as dastr
  from grades g1 
  full outer join grades g2 on g1.stuid = g2.stuid
  where g1.subject = algo and g2.subject = dastr;
  </code></pre>
  
  <p>But, mysql doesnt support full outer join. Is there any other way to resolve the problem?</p>
  
  <p>Also, case 2
  assuming there are unknown number of subjects in the table
  and the expected output would be</p>
  
  <pre><code>stuname subj1 subj2 subj3 ... subjn
  </code></pre>
  
  <p>I know I might be using procedure resolve it, is there any other way that I can use to compose columns in mySQL? </p>