PostgreSQL

Full Join in PostgreSQL

The terms Full Join or Full Outer Join have been used interchangeably. Full Join is the combination of a Left Join and Right Join. It displays all the records that are matched or unmatched. In PostgreSQL, the Full Join term has been used to fetch all the records whenever data has been matched to any tables, e.g., left or right. This article is helpful for those who lack an understanding of Full Join. To understand the concept of Full Join, we will be going through some examples. So, let’s open your PostgreSQL pgAdmin GUI from the taskbar of Windows 10.

Create Tables:

To understand Full Join, PostgreSQL users must have two tables in their system. So, we will be creating two tables and inserting records in them. Open the query editor within the Postgres database of pgAdmin. Firstly, we have created a table named “Wvegs” using the CREATE TABLE command in the query editor. This table represents the records for vegetables produced in the Winter season. The table contains columns WID and Wname. The INSERT INTO statement has been used to insert the values within both the table “Wvegs” columns. The “Run” icon from the taskbar of pgAdmin has been used to process the stated-below two queries.

CREATE TABLE Wvegs ( WID INT PRIMARY KEY, Wname VARCHAR (250) NOT NULL);

INSERT INTO Wvegs (WID, Wname) VALUES (1, ‘Onion’), (2, ‘Cabbage’), (3, ‘Cauliflower’), (4, ‘Carrot’), (5, ‘Broccoli’);

The success message on the output screen shows that the data has been inserted in the table properly. Let’s fetch the records of a table “Wvegs” using the “View/Edit Data” option by right-clicking on the table. We have added two more records in this table from the grid view directly. The query used to fetch the table is given as follows:

SELECT * FROM public.Wvegs ORDER BY WID ASC;

Another table, Svegs, has been created using CREATE TABLE command to store the records for vegetables produced in the Summer season. The INSERT INTO command is utilized here to insert records within this table. Both the tables have been created successfully, as the output shows.

CREATE TABLE Svegs ( SID INT PRIMARY KEY, Sname VARCHAR (250) NOT NULL);

INSERT INTO Svegs (SID, Sname) VALUES (1, ‘Tomato’), (2, ‘Potato’), (3, ‘Cucumber’), (4, ‘Mint’), (5, ‘Brinjal’);

The table “Svegs” with its inserted record can be fetched using the “View/Edit Data” option by right-clicking on the table “Svegs”. The “SELECT” command can also be used to do so, as illustrated below.

SELECT * FROM public.Svegs ORDER BY SID ASC;

Example 01: Simple Full Join

Let’s start with the implementation of Full Join on our very first example. We have been using the SELECT query within the editor of PostgreSQL GUI to select the records from the table “Svegs” and “Wvegs”. We have been fetching both the table’s column records within this query while applying the Full Join condition on the table Wvegs. All the records from both the tables will be displayed where the IDs of table “Svegs” and “Wvegs” are the same, e.g., 1 to 5. On the other hand, Full Join will replace NULL where the ID of Svegs doesn’t meet with the ID of the “Wvegs” table. The output of the stated Full Join query has been demonstrated in the snap.

SELECT SID, Sname, WID, Wname FROM Svegs Full Join Wvegs ON SID = WID;

If you want to replace the FULL JOIN keyword clause with the FULL OUTER JOIN, you can also do that in this example. So, we have replaced the Full Join with Full Outer Join in our query to see the results. The remaining query has been the same as stated beneath. We have found that both the queries work the same, and the output is quite similar as well. The output has been shown in the snap as well.

SELECT SID, Sname, WID, Wname FROM Svegs Full Outer Join Wvegs ON SID = WID;

Example 02: Table-Aliases Using Full Join

The above example has shown how the Full Join simply works perfectly to fetch the records. Now, we will look at the Full Join usage to do Table-aliases in the PostgreSQL database. Table-aliases is the simplest and powerful technique used within PostgreSQL and other databases to give the tables to be used in the Full join, some simplest names to avoid inconvenience upon the usage of difficult table names. It is the replacement of the table’s original name. While the Full Join works similarly as it does in the above example. So, we have used the same query as used in the above example with a little update. We have assigned the tables some new names as aliases, e.g., s and w. The query below shows the same output.

SELECT s.SID, Sname, WID, Wname FROM Svegs s Full Join Wvegs w ON s.SID = w.WID;

Example 03: Use of WHERE clause

We will be using the WHERE clause within the query having Full Join applied to it within this example. The instruction shows that the query must fetch all the records of both tables except the record of a vegetable name “Cucumber”. The output is showing all the data but missing the vegetables from both the tables having ID “3”. Due to this, the vegetable “Cauliflower” from the Wvegs table has also been ignored due to its ID “3”.

SELECT SID, Sname, WID, Wname FROM Svegs FULL JOIN Wvegs ON SID = WID WHERE Sname != ‘Cucumber’;

Example 04:

Let’s update the table “Wvegs” a little. We have added an extra column, “SID”, within it. We have added some records in some rows of the SID column, and some have been left empty purposely. The table has been saved by tapping on the Save icon from the taskbar.

After this update, we applied the Full Outer Join on the Svegs table instead of the Wvegs table as we did in the above examples. We have been fetching the records on behalf of the SID column mentioned in the Wvegs table and Svegs table while using aliases. The output displays all the records where the IDs from the SID of both tables matched. It displays null where the IDs are not similar within the column SID in table Svegs and Wvegs.

SELECT WID, Wname, Sname FROM Wvegs w FULL OUTER JOIN Svegs s ON s.SID = w.SID;

Example 05:

Let’s use the WHERE clause within the same above query used in the example. The Full Outer Join has been applied here to fetch the data row from both tables where the Wvegs table contains a NULL value in its column Wname. The output of the subsequent column value of another table, “Sname” in contrast to the NULL column of Wvegs, column “Wname” is “Cucumber”.

On the other hand, The output of the subsequent column values of another table “Wname”, in contrast to the NULL column of Svegs column “Sname” is “Carrot” and “Red Chilli”.

Conclusion:

This article contains FULL JOIN examples in PostgreSQL to gather all the data upon certain conditions got satisfied. The Full Join clause can achieve its goal while used within the SELECT command. The concept of Full Join gets easier upon the usage of table aliases and WHERE clauses. Usage of Full Join with mentioned clauses makes our article easier to understand and implement for PostgreSQL users.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.