Forum OpenACS Q&A: Response to Outer join Query for Users_files: Help

Collapse
Posted by Dan Wickstrom on
In this case, the correlated subquery in the target list is equivalent to an outer join. In fact, I believe that I read on one of the pg lists that they're going to do some optimizations in the planner to convert correlated subqueries to outer joins to improve performance. The equivalent outer join version is as follows:

select u.user_id,
       u.last_name,
       case when uf.owner_id is not null 
               then '*'::text 
               else null::text end as file_owner_flag
from users u 
       LEFT OUTER JOIN 
     users_files uf ON u.user_id = uf.owner_id;


 user_id | last_name | file_owner_flag 
---------+-----------+-----------------
       1 | foo       | 
       2 | bar       | *
       3 | baz       | 
(3 rows)

test=# 

In most cases the outer join version should be faster.