PostgreSQL Joins

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 -

ProductID	CustomerID	ProductCost	ProductName
1000	2	500	Keyboards
1001	4	100	USB
1002	1	1500	Bags

Consider the Customer table with the following records -

CustomerID	CustomerName	Age	Address	CustomerSalary
1	Aarav	36	Udaipur	35000
2	Vivaan	33	Mumbai	30000
3	Reyansh	28	Chennai	40000
4	Muhammad	29	Udaipur	50000
5	Sai	27	Mumbai	27000

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 −

ProductID	CustomerName	ProductName
1000	Vivaan	Keyboards
1001	Muhammad	USB
1002	Aarav	Bags

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.

Visit :

Outer Join

Discussion