title: MySQL - Join as zero if record Not IN
body: <p>To explain by example, take two tables, A and B</p>
  
  <pre><code>Table A
  id  foo
   1   x
   2   y
   3   z
  
  
  Table B
  id  aid bar
   1   3   50
   2   1  100
  </code></pre>
  
  <p>An example join</p>
  
  <pre><code>SELECT foo, bar FROM a, b WHERE a.id = b.aid;
  </code></pre>
  
  <p>Garners a result of</p>
  
  <pre><code>foo  bar
   z    50
   x   100
  </code></pre>
  
  <p>What I would like to do is get all values of <code>foo</code> and for any instances where there isn't a corresponding <code>bar</code> value, return 0 for that column.</p>
  
  <pre><code>foo  bar
   z    50
   y     0
   x   100
  </code></pre>
  
  <p>My best guess was something along the lines of</p>
  
  <pre><code>SELECT foo, bar AS br FROM a, b 
  WHERE a.id = b.aid 
  OR a.id NOT IN (SELECT aid FROM b);
  </code></pre>
  
  <p>But that returns duplicates and non-zero values for bar.</p>
  
  <p>Possible?</p>