PostgreSQL Full JoinIn 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 JoinThe 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 JoinLet us see an example to understand how the PostgreSQL Full Outer join works: To join two tables by using PostgreSQL Full Outer JoinFor 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: Table1: Summer_fruits Output Table2: Winter_fruits Output 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: Output After implementing the above command, we will get the below result: Working of PostgreSQL Full Outer Join
Table-aliasing with PostgreSQL Full JoinWe 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: Output After implementing the above command, we will get the below output: PostgreSQL Full Join using where clauseWe 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: Output On executing the above command, we will get the following output: Overview In the PostgreSQL Full join or Full Outer Join section, we have learned the following topics:
Next TopicPostgreSQL Cross Join |