PostgreSQL 9.3 Lateral Part2: The Lateral Left Join   

In the last article we said you can't have a LEFT JOIN with LATERAL. Turns out we were mistaken and YES indeed you can and when you do it is equivalent or more powerful than SQL Server's OUTER APPLY.

Let us say that in our query we wanted all my zips returned but for ones that had keys where the value is in a certain range, we want those keys returned. we'd do this. The fact we need all even if they have no such keys necessitates us putting the condition in the ON rather than the WHERE.

SELECT zip, (h).key, (h).value As val
 FROM zcta5 AS foo 
   LEFT JOIN LATERAL each(hstore(foo) - 'zip'::text)  As h 
    ON ((h).value BETWEEN '12345' and '14567')
    ORDER BY zip
   limit 5;

Output would be

 zip  |  key   |   val
------+--------+----------
00601 |        |
00602 |        |
00603 |        |
00606 | awater | 12487.00
00610 | hu10   | 12618