The Powerful LATERAL JOIN in PostgreSQL

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.

city	population	area
Mumbai	12.5	603.4
Delhi	19	1484
Kolkata	14.9	206.1
Chennai	7	426

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:

city	most_similar_city_area
Mumbai	Chennai
Delhi	Mumbai
Kolkata	Chennai
Chennai	Mumbai

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.

---

Found this post helpful? Then check out further posts on PostgreSQL on iotespresso.com. Also, follow IoT Espresso on Twitter to get notified about every new post.