A join is used to view information from multiple tables.A PostgreSQL join statement is used to join the data or records of two or more tables based on the corresponding column between them.There are four different types of PostgreSQL joins: Inner, Left, Right, and Full.The easiest and most common way to learn the difference between PostgreSQL joins is to use the Venn diagram.
Joins Syntax :
The syntax of the Joins statement is −
SELECT col_name1,col_name2,... FROM table1 join table2 ON table1.col_name = table2.col_name join table3 ON table2.col_name = table3.col_name
PostgreSQL Join Example :
Consider the Product table with the following records -
Consider the Customer table with the following records -
TEST CASE 1 :- The "CustomerID" column in the "Product" table refers to the "CustomerID" in the "Customer" table. The relationship between the two tables above is the "CustomerID" column.
We can create the following PostgreSQL statement (containing an INNER JOIN), which selects the records that have matching values in both tables.
SELECT Product.ProductID, Customer.CustomerName, Product.ProductName FROM Product INNER JOIN Customer ON Product.CustomerID=Customer.CustomerID;
The result for the respective PostgreSQL query is as follows −
Different Types of PostgreSQL JOINs
Here are the different types of the JOINs in PostgreSQL:
Select all records from Table A and Table B, where the join condition is met. PostgreSQL inner joins are used to join data or records of two or more tables based on matching values in both tables. The Inner Join will create the resulting set until the condition is satisfied by combining all rows from both tables.
PostgreSQL Left Join is used to return all data or records from the Table 1 plus match records to tables 2. If there is no match, the result from the right will be NULL.
PostgreSQL Right Join is used to return all data or records from the Table 2 plus match records to tables 1. If there is no match, the result from the right will be NULL.
PostgreSQL Full Join is used to return all data or records when a match occurs in a Table 1 or Table 2 record and NULLs have to be filled in for missing matches on either side. It is also known as Full (OUTER) JOIN.