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:

SELECT columns  
FROM table1  
FULL [OUTER] JOIN table2  
ON table1.column = table2.column;  

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:

• Firstly, we will define the column list from both tables, where we want to select data in the SELECT condition.
• Then, we will specify the Right table, which is table 2 in the FROM clause.
• And lastly, we will describe the Left table, which is table 1 in the Full Outer Join clause, and write the join condition after the ON keyword.

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:

CREATE TABLE Summer_fruits (
SF_ID INT PRIMARY KEY,
Summer_fruits_name VARCHAR (250) NOT NULL);

The below command is used to create the Winter_fruits table:

CREATE TABLE Winter_fruits (
WF_ID INT PRIMARY KEY,
Winter_fruits_name VARCHAR (250) NOT NULL);

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:

INSERT INTO Summer_fruits (SF_ID, Summer_fruits_name)
VALUES VALUES(1,'Mango'),
(2,'Watermelon'),
(3,'Apples'), 
(4,'Guava'),
(5,'Pineapple'),
(6,'Musk Melon');

In the below command, we are inserting the values in the Winter_fruits table:

INSERT INTO Winter_fruits (WF_ID, Winter_fruits_name)
VALUES(1,'Grape'),
(2,'Apples'), 
(3,'Mango'),
(4,'Pears'),
(5,'Pineapple'),
(6,'Cranberries'),
(7,'Bananas');

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

Select * from Summer_fruits;

Output

Table2: Winter_fruits

Select * from 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):

SELECT SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits
FULL JOIN 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:

ON SF_ID = WF_ID;
SELECT SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits
Full Outer Join Winter_fruits
ON SF_ID = WF_ID;

Output

After implementing the above command, we will get the below result:

Working of PostgreSQL Full Outer Join

• It is used to get the records of both the left table (Summer_fruits) and the right table (Winter_fruits).
• If a row from Summer_fruits, which is Table1 or Left Table matches a row in Winter_fruits, which is Table2 or Right table, then the result row will contain columns that came from columns of rows from both tables.
• If the rows in the joined tables are not similar, then the Full Outer Join place NULL values for every column of the table.

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:

SELECT s.SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits s
FULL JOIN Winter_fruits w
ON s.SF_ID =w.WF_ID;

Output

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:

SELECT SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits
FULL JOIN Winter_fruits
ON SF_ID = WF_ID
WHERE  Summer_fruits_name != '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:

• We used the Full join clause to select data from two tables or more than two tables.
• We used the Full join condition with table aliasing, and WHERE clause.