Subqueries appearing in FROM can be preceded by the key word LATERAL.
Also it is commonly used when value comparison in subqueries is required.
IN is slower as it collects all results from the Subquery and processes it.
More interesting cases, which can't be reduced to a plain join,
arise when the Subquery involves grouping or aggregation.
In this case all the Subquery rows must yield arrays of identical dimensionality,
else the result would not be rectangular.
When running IN, it collects all results from the Subquery and presents them for further processing, and this process takes a while.
If the Subquery's output column is of an array type,
the result will be an array of the same type but one higher dimension;
When an aggregate expression appears in a Subquery(see Section 4.2.9 and Section 9.16),
the aggregate is normally evaluated over the rows of the Subquery.
I need to get the record with the MAX id from this joined table, but I only need the top
row to be joined with the main query on this Subquery.