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