PostgreSQL came up with the concept of LATERAL JOIN in version 9.3. You can think of LATERAL JOIN as a ‘for each’ kind of a join. Often you need to perform a join wherein the condition has to be applied based on the value of a column in each row.
The example below will give you an idea.
Say you have a table ‘cities’, which contains the land area, population, etc. of different cities. A short example is given below.
Now, suppose you are asked to find the most similar city (in terms of area), for each city in the table. You can already see the ‘for each’ requirement coming in. You can give it a thought. The conventional joins won’t work here. Before PostgreSQL 9.3, you’d have had to define a separate function to get this done. But then, come in LATERAL JOIN.
The code snippet below shows how this can be done using lateral join.
SELECT t1.city, t2.city as most_similar_city_area from cities t1 CROSS JOIN LATERAL (SELECT city, abs(area-t1.area)*100.0/t1.area as perc_area_diff from cities where city != t1.city ORDER BY perc_area_diff asc LIMIT 1) t2
Here, you can see that we are referencing the columns of t1 (area and city) directly in t2. For each row, the value of these columns in that row will be used for performing the required computation.
We have performed CROSS JOIN here. CROSS JOIN means an m x n join. If you are doing t1 CROSS JOIN t2, and if t1 has m rows, and t2 has n rows, then the final output will have m x n rows, i.e., t2 is replicated for each row of t1. Since we have LIMIT 1 in t2, we will just have 1 row of t2 for each row of t1. The final output is:
You can verify this output yourself, using the area numbers. As you can see, this computation would have been very difficult to perform in PostgreSQL, in the absence of LATERAL JOIN. Alternatively, the presence of LATERAL JOIN makes it possible to perform several advanced computations in PostgreSQL, in just a couple of lines of code.