PostgreSQL Join

What are Joins in PostgreSQL?

In a relational database, information is typically stored across multiple tables, each representing a specific entity or concept. For example, in a retail database, one table might store customer information, while another holds details of orders placed by customers. Often, the need arises to combine data from these tables to gain meaningful insights or retrieve relevant information. This is where joins in postgresql come into play.

Joins in postgresql context are operations that merge rows from two or more tables based on a related column or key. The process of joining tables is fundamental to relational databases, as it allows users to create cohesive views of data stored across different tables.

The primary benefit of using joins in postgresql is the ability to avoid data duplication. Rather than storing all information in a single large table, data is distributed across multiple tables, each with a specific focus. This not only improves data organization but also ensures data consistency and reduces redundancy.

Join Types in PostgreSQL

Joins in postgresql are implemented using a variety of join types, each serving a specific purpose:

• CROSS JOIN
• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Now to understand all the joins in postgresql better, let's create some data. Let's say we have Customers and Orders table which have the following data.

Customers Table:

Orders Table:

The CROSS JOIN

The CROSS JOIN is the simplest type of joins in PostgreSQL. It combines every row from the first table with every row from the second table, resulting in a Cartesian product. The Cartesian product is the complete set of all possible combinations between the rows of both tables.

For instance, consider two tables: table1 with three rows and table2 with four rows. A CROSS JOIN between these tables would produce 3 x 4 = 12 rows in the result set, combining each row from table1 with every row from table2.

Example:

The CROSS JOIN is rarely used on its own in practical scenarios due to its potential to generate an enormous number of rows, especially when joining large tables.

Now let's try using Cross Join in the Customers and Orders table

The Cross Join of the 2 tables will give the following output:

The INNER JOIN

The INNER JOIN, also known as the EQUIJOIN, is one of the most commonly used joins in PostgreSQL. It returns only the rows that have matching values in both tables based on the specified join condition.

When using an INNER JOIN, rows from both tables that satisfy the join condition are included in the result set, while rows without matching values in the joined table are excluded.

Example:

Now let's try using Inner Join in the Customers and Orders tables.

The Inner Join of the 2 tables will give:

In this example, the orders table is joined with the customers table using the customer_id column as the join condition. The result will contain only the rows where there is a match between the customer_id in the orders table and the id in the customers table.

INNER JOINS are especially valuable when working with related data spread across multiple tables. They allow us to extract meaningful information by combining relevant rows from different tables.

The LEFT OUTER JOIN

The LEFT OUTER JOIN, also known as the LEFT JOIN, retrieves all rows from the left table and matching rows from the right table. If there is no match in the right table, the result will include NULL values for the right table columns.

Example:

Now let's try using Left Outer Join in the Customers and Orders tables.

The Left Outer Join of the 2 tables will give:

In this example, the Customers table is joined with the Orders table using the customer_id column as the join condition. The result will contain all rows from the Customers table, and for each matching row in the Orders table, the corresponding data will be included. If there is no match in the Orders table, the columns from the Orders table will have NULL values.

LEFT OUTER JOINS are commonly used when you want to retrieve all records from one table and the matching records from another table, with the possibility of some unmatched records.

The RIGHT OUTER JOIN

The RIGHT OUTER JOIN, also known as the RIGHT JOIN, is the reverse of the LEFT OUTER JOIN. It retrieves all rows from the right table and matching rows from the left table. If there is no match in the left table, the result will contain NULL values for the left table columns.

Example:

Now let's try using Right Outer Join in the Customers and Orders tables.

The Right Outer Join of the 2 tables will give:

In this example, the Customers table is joined with the Orders table using the customer_id column as the join condition. The result will contain all rows from the Orders table, and for each matching row in the Customers table, the corresponding data will be included. If there is no match in the Customers table, the columns from the Customers table will have NULL values.

RIGHT OUTER JOINS are less commonly used than LEFT OUTER JOINS, as most scenarios can be handled using LEFT OUTER JOINS. However, they can be helpful when you want to ensure that all records from the right table are included in the result.

PostgreSQL Right Join with Where Clause

In PostgreSQL, the RIGHT OUTER JOIN can be used in conjunction with a WHERE clause to further filter the results based on specific conditions.

Example:

Now let's try using Right Outer Join in the Customers and Orders tables with a where clause.

The above command will give:

In this example, the RIGHT OUTER JOIN is applied between the Customers and Orders tables on the customer_id column. The WHERE clause is then used to limit the results to only those where the total_amount is greater than '100'.

This combination of RIGHT OUTER JOIN and WHERE clause enables developers to perform targeted queries, retrieving only the relevant data from both tables.

The FULL OUTER JOIN

The FULL OUTER JOIN retrieves all rows from both the left and right tables, including matching rows from both sides. If there is no match, NULL values are included for the non-matching side.

Example:

Now let's try using Full Outer Join in the Customers and Orders tables.

The above command will give:

This command will return all rows from both the Customers and Orders tables, combining them based on the customer_id. If there's no match in either table, the respective columns will contain NULL values.

FULL OUTER JOINS are used when you want to combine all records from both tables, ensuring that no data is omitted. This type of join is less common but can be valuable when dealing with datasets that have varying and potentially incomplete information.

PostgreSQL Full Outer Join using Where Clause

As with other joins in postgresql, the FULL OUTER JOIN can also be combined with a WHERE clause to further filter the data based on specific conditions.

Example:

Now let's try using Full Outer Join in the Customers and Orders tables with a where clause.

The above command will give:

This command performs a full outer join between the Customers and Orders tables, filtering only the rows where total_amount is NULL or greater than 100.

This combination of FULL OUTER JOIN and WHERE clause enhances the flexibility of queries, allowing developers to extract precisely the data they require.