PostgreSQL Full Join
In this section, we are going to understand the working of PostgreSQL Full join, which is used to return all records when there is a match in the left table or right table records. We also learn how to use table-aliasing, WHERE clause with the help of the PostgreSQL Full Outer join clause.
What is the PostgreSQL Full Join or Full Outer Join clause?
The PostgreSQL Full Join or Full Outer Join is used to return all records when there is a match in the left table or right table records. The main objective of a Full Outer Join is that it will combine the outcome of PostgreSQL Left Join and PostgreSQL Right Join clauses and returns all similar or unmatched rows from the tables on both sides of the join clause.
The following Venn diagram displays the PostgreSQL Full Outer Join where we can easily understand that the Full Outer Join returns all the data from both the Left table and Right table:
Syntax of PostgreSQL Full Outer Join
The syntax for Full Outer Join or Full Join is as following:
In the above syntax, The Full Outer Join keyword is used with the SELECT command and must be written after the FROM Keyword, and the OUTER keyword is optional.
We will follow the below steps to combine the Left and Right tables with the help of the Full Join or Full Outer Join condition:
Example of PostgreSQL Full Join
Let us see an example to understand how the PostgreSQL Full Outer join works:
To join two tables by using PostgreSQL Full Outer Join
For this, we will create two tables named Summer_fruits and Winter_fruits table with the help of the CREATE command and insert some values using the INSERT command.
Firstly, we are going to create Summer_fruits and Winter_fruits tables by using the CREATE command:
The below command is used to create the Winter_fruits table:
The Summer_fruits and Winter_fruits tables have been successfully created on executing the above commands.
Once both the tables have been generated, we are ready to insert some values into it by using the INSERT command as follows:
In the below command, we are inserting the values in the Winter_fruits table:
After creating and inserting the values in the Summer_fruits and Winter_fruits table, we will get the following output on executing the below command:
After executing the above command, we will get the data from the Winter_fruits table:
The below query is used to select records from both tables (Summer_fruits and Winter_fruits):
Or we use the Full Outer Join keyword in place of Full Join keyword in the above query, we will get similar output:
After implementing the above command, we will get the below result:
Working of PostgreSQL Full Outer Join
Table-aliasing with PostgreSQL Full Join
We will use table aliases to assign the joined tables short names to make the command more understandable because sometimes writing the complete table lead us to tedious process.
In the below command, we will use the table aliasing, and it returns a similar outcome:
After implementing the above command, we will get the below output:
PostgreSQL Full Join using where clause
We can also use the Full join with a WHERE condition. The WHERE clause allows us to return the filter outcome.
In the below example, we will select rows from both tables Summer_fruits and Winter_fruits where Summer_fruits_names is not equal to Mango:
On executing the above command, we will get the following output:
In the PostgreSQL Full join or Full Outer Join section, we have learned the following topics: