DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Updated on

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)
Enter fullscreen mode Exit fullscreen mode

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.data1, t2.data2
 FROM table1 t1
 INNER JOIN table2 t2 ON =
cte2 AS (
 SELECT, c1.data1, c1.data2, t3.data3
 FROM cte1 c1
 INNER JOIN table3 t3 ON =
cte3 AS (
 SELECT, c2.data1, c2.data2, c2.data3, t4.data4
 FROM cte2 c2
 INNER JOIN table4 t4 ON =
cte4 AS (
 SELECT, c3.data1, c3.data2, c3.data3, c3.data4, t5.data5
 FROM cte3 c3
 INNER JOIN table5 t5 ON =
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)