How to join tables in PostgreSQL using the WITH clause?

To join tables in PostgreSQL using the WITH clause (also known as Common Table Expressions or CTEs), you first need to define these tables and the keys on which they will be joined. Let’s consider an example where we have five tables linked by a common key, such as id.

Suppose we have the following tables:

table1(id, data1)
table2(id, data2)
table3(id, data3)
table4(id, data4)
table5(id, data5)

Here, each table contains an id column that will be used for joining.

The join will be conducted using an inner join, which means that only those rows that have a corresponding match in all tables will be included in the result. Here’s how you can do this using WITH:

WITH cte1 AS (
 SELECT t1.id, t1.data1, t2.data2
 FROM table1 t1
 INNER JOIN table2 t2 ON t1.id = t2.id
),
cte2 AS (
 SELECT c1.id, c1.data1, c1.data2, t3.data3
 FROM cte1 c1
 INNER JOIN table3 t3 ON c1.id = t3.id
),
cte3 AS (
 SELECT c2.id, c2.data1, c2.data2, c2.data3, t4.data4
 FROM cte2 c2
 INNER JOIN table4 t4 ON c2.id = t4.id
),
cte4 AS (
 SELECT c3.id, c3.data1, c3.data2, c3.data3, c3.data4, t5.data5
 FROM cte3 c3
 INNER JOIN table5 t5 ON c3.id = t5.id
)
SELECT * FROM cte4;

Explanation:
cte1: The first CTE joins table1 and table2 by id. The result of this join, including id, data1, and data2, is used in the next CTE.
cte2: The second CTE takes the result from cte1 and joins it with table3 using the same key id. The result now includes data3.
cte3: The third CTE continues the process by adding data from table4 (data4).
cte4: The final CTE adds data from table5 (data5), completing the series of joins.
Final SELECT: Retrieves all data from the last CTE.

By using this structure, you can effectively manage and track each step of the joining process, which is particularly useful for debugging complex queries.

It also makes the code more readable and understandable for other developers.

[e-mail]