How To Dev

Introduction When joining multiple tables together (such as 3 tables or more), PostgreSQL never joins all tables at the same time, but at any single point in time, it can only join 2 datasets. Suppose you need to join multiple tables, the process of bridging steps will be performed as follows First, Postgres will select 2 tables to join together to create an Intermediate Result Postgres will treat the Intermediate Result as a completely new table and will use it to join with the next table to create the next Intermediate Result This process repeats over and over until all tables have been joined. Algorithms used When joining multiple tables, Postgres still uses algorithms like Nested Loop Join, Hash Join and Merge Join But corresponding to each dataset and the index of the tables, Postgres will choose different algorithms when joining rather than fixing a single algorithm For example, when joining 4 tables A, B, C and D together Suppose table A is small with about 100 rows joining with...

Introduction When performing a table join that compares multiple columns simultaneously (such as ON A.col1 = B.col1 AND A.col2 = B.col2), PostgreSQL will still use familiar algorithms like Hash Join, Merge Join or Nested Loop Join. However, the way these algorithms process multiple columns involves very distinctive strategies as follows. Hash Join This is usually the number one choice for Postgres when joining multiple columns on large tables. Instead of hashing each individual column, Postgres performs string concatenation to hash the combination of all those columns at the same time. Build Phase: Postgres takes the values of all participating join columns and groups them together (like combining value = col1 + col2 + col3), then inputs this entire combined string into the Hash Function to calculate the Hash Number to be placed into the Bucket. Probe Phase: Next, it also combines the corresponding columns of the remaining table to hash similarly and then compares it with the Hash Tabl...

Introduction In this article, we will learn about Merge Join. How It Works Simply put, how Merge Join works is like sorting two lists in ascending order first, then placing them side by side for comparison. Assuming we need to compare the id column of two tables, A and B, the specific process is as follows: Step 1: Sort Phase PostgreSQL must ensure that both lists are already sorted by the order of the join column (for example, from smallest to largest). If the two tables are already sorted, such as when the join column is a Primary Key or has an Index, Postgres will skip this step and jump to step 2. Step 2: Merge Phase PostgreSQL will place two pointers starting from the first row of the two tables and begin comparing: If the results on both sides are equal: Return the result, then move the pointer in table B to the next row to continue checking (since data in a column can be duplicated). If the id in table A is smaller: Move the table A pointer to the next row. If the id in table A ...

Introduction In SQL and specifically in PostgreSQL, there are 4 types of Joins as follows: INNER JOIN: Only retrieves records that have a match in both tables. LEFT JOIN: Retrieves all records from the left table, and if there is no match in the right table, the values are set to NULL. RIGHT JOIN: The opposite of LEFT JOIN, rarely used because it can be rewritten in reverse using LEFT JOIN. FULL OUTER JOIN: Retrieves all records from both tables, filling with NULL where there is no match. Nested Loop Join When executing a join, the Postgres Optimizer automatically performs an analysis based on the datasets of the 2 tables to select the most efficient and suitable algorithm for the current situation First, let us look at the Nested Loop Join. This is the most basic join algorithm, and its operational mechanism is very straightforward: it takes each item from one table to compare it with every item from the other table, acting exactly like two nested for loops in programming Use cases Th...