PostgreSQL’s New LATERAL Join Type

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read.<p>It&#x27;s also great for set returning functions. Even cooler, you don&#x27;t need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically:<p><pre><code> -- NOTE: WITH clause is just to fake a table with data: WITH foo AS ( SELECT &#x27;a&#x27; AS name , 2 AS quantity UNION ALL SELECT &#x27;b&#x27; AS name , 4 AS quantity) SELECT t.* , x FROM foo t -- No need to say &quot;LATERAL&quot; here as it&#x27;s added automatically , generate_series(1,quantity) x; name | quantity | x ------+----------+--- a | 2 | 1 a | 2 | 2 b | 4 | 1 b | 4 | 2 b | 4 | 3 b | 4 | 4 (6 rows)</code></pre>

I&#x27;m a huge believer in postgres&#x27; json store, but anything above the basic queries can get a bit messy. Lateral joins really clean up aggregate data queries on json columns. E.g.<p><pre><code> SELECT id, COUNT(keys) FROM users, LATERAL json_object_keys(login_history) keys GROUP BY id;</code></pre>

As someone who only works now and then with Postgres, this is what made it click for me:<p>&quot;Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter.&quot;

The `sum(1)` and `order by...limit` approach really isn&#x27;t the best way to build the funnel. And if you take another approach then this could have easily been built with normal left joins.<p>Also, you should probably show some explain plans before making this claim: &quot;Without lateral joins, we would need to resort to PL&#x2F;pgSQL to do this analysis. Or, if our data set were small, we could get away with complex, inefficient queries.&quot;<p>Here&#x27;s a comparison of the explain plan from your query without the `sum(1)` and `order by...limit` business and a query using only left joins (no use of lateral): [link redacted]. Note, I ran this against an empty copy of your exact table (no data, no statistics). However, the explain plans are the same.<p>My understanding is that lateral was really meant for set returning functions like generate_series as others have already mentioned.<p>Edit: I should mention I know you were just trying to demonstrate how lateral works and that it is always good to see people writing about new Postgres features!

&gt; Without lateral joins, we would need to resort to PL&#x2F;pgSQL to do this analysis. Or, if our data set were small, we could get away with complex, inefficient queries.<p>I don&#x27;t buy the performance benefit over derived tables with properly indexed fields for this example. However, I&#x27;d definitely use this more so with functions.

Here&#x27;s another explanation from the Postgres docs with additional examples: <a href="http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html#QUERIES-LATERAL" rel="nofollow">http:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.3&#x2F;static&#x2F;queries-table-expr...</a>

seems similar to CROSS APPLY and OUTER APPLY from mssql <a href="http://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx" rel="nofollow">http:&#x2F;&#x2F;technet.microsoft.com&#x2F;en-us&#x2F;library&#x2F;ms175156%28v=sql....</a>

Fantastic. This is <i>exactly</i> what I wanted earlier in the week. I ended up just writing some python to do the looping for me as it was only a few thousand rows and I was optimizing for developer time (i.e. mine).

How does this relate to WITH RECURSIVE (CTEs)?<p><a href="http://www.postgresql.org/docs/8.4/static/queries-with.html" rel="nofollow">http:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;8.4&#x2F;static&#x2F;queries-with.html</a>

cross join lateral also works with json arrays. Select your json array then CROSS JOIN LATERAL with a jsonb_to_recordset() to take your json array into individual columns.

From the many specific comments on here, it sounds like most people don&#x27;t use an ORM. Is that the case?<p>Not trolling or trying to start a flame, I&#x27;m genuinely curious as to how people here get stuff done.

Nice read.<p>Somethings i would have liked to have read&#x2F;seen though are, the hardware specs, database dimensions (nr of entries in tables etc) and query times.<p>Just my penny. Hope you can spend it still somewhere :)

So it seems this does the same thing as putting the subquery in the list of columns to return, except more efficient.<p>i.e.<p><pre><code> SELECT a, (SELECT b FROM ...) b FROM ....</code></pre>

Is this the same as a correlated sub-query (something that exist in Teradata)?