title: Can one efficiently LEFT OUTER JOIN a subset of the left table's rows in Postgres?
body: <p>Let's say I have the following tables:</p>
  
  <pre><code>table_1                  table_2
  id_a    name             id_a    id_b
  1       c                1       1
  2       a                1       2
  3       b                2       1
                           2       2
  </code></pre>
  
  <p>Now consider the following LEFT OUTER JOIN:</p>
  
  <pre><code>SELECT *
  FROM table_1
  LEFT OUTER JOIN table_2 USING (id_a)
  
  id_a    name  id_b  
  1       c     1
  1       c     2
  2       a     1
  2       a     2
  3       b
  </code></pre>
  
  <p>Now imagine that 'FROM table_1' is actually a complex sub-query, like:</p>
  
  <pre><code>SELECT * FROM huge_table WHERE expensive_conditions_producing_three_rows
  </code></pre>
  
  <p>Is it possible to write a query that only joins against the left row with the minimum name, without re-running the sub-query entirely?  You can assume that you have some control over the sub-query, i.e. you could add an ORDER BY if necessary.</p>
  
  <p>In other words, the end result should look like this:</p>
  
  <pre><code>id_a    name  id_b
  1       c
  2       a     1
  2       a     2
  3       b
  </code></pre>
  
  <p>I considered using SELECT INTO to place the sub-query results in a temporary table.  Then it wouldn't be a problem to compute the minimum for use in a JOIN ON condition.  But I'd prefer to avoid this unless it's the only solution.</p>
  
  <p>Edit: I'll wait a couple of days and then accept the best solution, regardless of PG version.  One that works in PG 8.3 and earlier would be greatly appreciated, though.</p>