title: postgresql XOR Join on array value or null?
body: <p>I have two tables, Foo and Bar, I'd like to join them together so BarID gets the prize based on having a key that is in the array, or if there's no array set up, it defaults as if every key unlocks the prize.</p>
  
  <pre><code>&gt;Table Foo
  fooID | someArray    | prize
  ----------------------------------
   1    | {10,20,30}   | 'Winner'
   2    | {10}         | 'Grand prize'
   3    | null         | 'Participant'
  </code></pre>
  
  <p>(That's not a string for someArray, it's a valid <a href="http://www.postgresql.org/docs/current/interactive/arrays.html" rel="nofollow">postgresql type</a>.)</p>
  
  <pre><code>&gt;Table Bar 
  BarID | Key
  ------------
   1    | 10
   2    | 20
   3    | 30
   4    | 40
   5    | 40
   6    | 40
  </code></pre>
  
  <p>I've tried this below:</p>
  
  <pre><code>SELECT
      Foo.fooID,
      Bar.prize
  FROM Foo
  LEFT JOIN Bar ON Bar.Key = ANY(someArray)
  </code></pre>
  
  <p>I have minimal success with this, when I put in a WHERE clause such as</p>
  
  <pre><code>WHERE Bar.BarID = 3
  </code></pre>
  
  <p>My results are simply 'Winner'm but I also want to get 'Participant'. If I set up my WHERE to include nulls:</p>
  
  <pre><code>WHERE 
      Bar.BarID = 3
    OR
      Foo.someArray = null
  </code></pre>
  
  <p>I still do not get my null values out.</p>
  
  <p>No where clause returns all combinations of values, including the nulls.</p>
  
  <p>I'm not really sure how to get this behaviour I want. I can get all, or I can get just one set of values, but not both.</p>
  
  <p>To give examples, if I were to do the join for BarID = 1, I should get "Winner", "Grand prize" and "participant". If I were to select for BarID 5, I would only get Participant.</p>