title: Using UNNEST with a JOIN
body: <p>I want to be able to use <code>unnest()</code> function in PostgreSQL in a complicated SQL query that has many <code>JOIN</code>s. Here's the example query:</p>
  
  <pre><code>SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id
  FROM mentions
  INNER JOIN taggings ON taggings.mention_id = mentions.id
  INNER JOIN tags ON tags.id = taggings.tag_id
  WHERE mentions.taglist &amp;&amp; ARRAY[9] AND mentions.search_id = 3
  GROUP BY tags.parent_id
  </code></pre>
  
  <p>I want to eliminate the <code>taggings</code> table here, because my <code>mentions</code> table has an <strong>integer array</strong> field named <strong>taglist</strong> that consists of all linked tag ids of <code>mentions</code>.</p>
  
  <p>I tried following:</p>
  
  <pre><code>SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id 
  FROM mentions 
  INNER JOIN tags ON tags.id IN (SELECT unnest(taglist))
  WHERE mentions.taglist &amp;&amp; ARRAY[9] AND mentions.search_id = 3 
  GROUP BY tags.parent_id 
  </code></pre>
  
  <p>This works but brings different results than the first query.</p>
  
  <p>So what I want to do is to use the result of the <code>SELECT unnest(taglist)</code> in a <code>JOIN</code> query to compensate for the <code>taggings</code> table.</p>
  
  <p>How can I do that?</p>
  
  <p><strong>UPDATE:</strong> <code>taglist</code> is the same set as the respective list of tag ids of mention.</p>