Is it possible to effectively include a LEFT OUTER JOIN a subset of the rows in the left table in Postgres?

Question

Let's say I have the following tables:

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 

Now consider the following LEFT OUTER JOIN:

 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 

Now imagine that 'FROM table_1' is actually a complex subquery, for example:

 SELECT * FROM huge_table WHERE expensive_conditions_producing_three_rows 

Is it possible to write a query that joins only the left line with a minimum name, without restarting the subquery? You can assume that you have some control over the subquery, i.e. You can add ORDER BY if necessary.

In other words, the end result should look like this:

 id_a name id_b 1 c 2 a 1 2 a 2 3 b 

I considered using SELECT INTO to put subquery results in a temporary table. Then it would not be easy to calculate the minimum for use in the JOIN ON state. But I would rather avoid this if this is not the only solution.

Edit: I will wait a couple of days and then make the best decision, regardless of the PG version. However, one that works in PG 8.3 and earlier should be welcomed.

Answer 1

Use CTE (generic table expression) for this (available for PostgreSQL 8.4 or later):

 WITH cte AS ( SELECT id_a, name FROM table_1 WHERE expensive_conditions_producing_three_rows ) SELECT c.id_a, c.name, t2.id_b FROM cte c LEFT JOIN table2 t2 ON t2.id_a = c.id_a AND t2.name = (SELECT min(name) FROM cte) 

Answer 2

Using the Window Function (available from PostgreSQL 8.4):

 SELECT * FROM ( SELECT * , ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum FROM table_1 ) AS a LEFT JOIN table_2 AS b ON (join condition) AND a.RowNum = 1