Скачать или смотреть How to Properly Join PostgreSQL Tables with Multiple Common Columns

Discover the best method to join PostgreSQL tables with shared columns for clearer transaction records.
---
This video is based on the question https://stackoverflow.com/q/70366238/ asked by the user 'krown_loki' ( https://stackoverflow.com/u/10250453/ ) and on the answer https://stackoverflow.com/a/70366347/ provided by the user 'JRA' ( https://stackoverflow.com/u/6711884/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Join postgres table that has two common columns of another table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Properly Join PostgreSQL Tables with Multiple Common Columns

In the world of databases, managing relationships between tables is a crucial skill, especially when it comes to joining tables that have multiple common columns. Many users face challenges in retrieving meaningful data from their tables in a clear and organized manner. Today, we're going to explore how to efficiently join two PostgreSQL tables with two common columns, using a practical example with Transactions and Addresses tables.

The Problem

Let’s start with the tables at hand:

Transactions Table

The Transactions table keeps a record of transfers, including the sender and receiver addresses:

idaddress_fromaddress_tovalue1121000212500Addresses Table

The Addresses table shows what each address corresponds to:

idaddress1address12address2Now, the goal is to retrieve all transactions while replacing the address_from and address_to IDs with their respective address names.

When a basic join is executed, the results returned may appear redundant and not as intended:

[[See Video to Reveal this Text or Code Snippet]]

Current Result

This query yields:

idaddressvalue1address110001address110002address25002address2500However, you might want to see results like this:

idaddress_fromaddress_tovalue1address1address210002address1address2500The Solution

Using Multiple Joins

To obtain the desired format, you need to perform two joins with the Addresses table: one for address_from and another for address_to. Here's how you can structure the SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

SELECT statement: This part specifies which fields you want in your result set:

tr.id: The transaction's ID.

adf.address AS address_from: The address corresponding to address_from.

adt.address AS address_to: The address corresponding to address_to.

tr.value: The transaction value.

FROM clause: We're starting with the transactions table.

JOIN clauses:

The first join (JOIN addresses adf ON tr.address_from = adf.id) matches addresses for those found in the address_from column.

The second join (JOIN addresses adt ON tr.address_to = adt.id) matches addresses for those in the address_to column.

The Desired Output

When you execute the revised query, you'll get:

idaddress_fromaddress_tovalue1address1address210002address1address2500This output presents a clear and organized view of transactions with addresses instead of IDs.

Efficiency of Joins

Is It Expensive?

It's a valid concern to consider the performance implications of multiple joins, especially as data grows:

Join Performance: While joining on two columns may increase complexity, if your tables are indexed properly, the performance impact should be minimal.

Indexes: Ensure that the id fields in the Addresses table are indexed for optimal join performance.

Conclusion

Joining tables in PostgreSQL with multiple common columns can seem daunting at first, but with the correct structuring of your query, it’s both achievable and efficient. The use of multiple joins allows you to enrich your results, making data easier to interpret.

By following the steps outlined above, you can extract the information you need without unnecessary redundancy. Happy querying!