Relational databases such as MySQL, PostgreSQL, etc. store data in multiple tables that are related through unique keys assigned to them. There is always a need to combine two or more tables in the database to produce a resultant table containing comprehensive information. JOINs are used to extract records from multiple locations called tables based on some conditions in the database.

This guide will explain the use of FULL OUTER JOIN in PostgreSQL.

How Does FULL OUTER JOIN Work in Postgres?

FULL OUTER JOIN in PostgreSQL is used to generate all the records from all the tables involved in the JOIN. It is the combination of LEFT and RIGHT JOIN as it provides all the matched and unmatched records from all the tables. It only works when it finds a match of a column name in both or all tables involved in the operation:

Syntax

Following is the syntax of the FULL OUTER JOIN:

SELECT <Columns>
FROM <TableA>
FULL [OUTER] JOIN <TableB> ON <TableA.id> = <TableB.id>;

This code block:

• Selects a column list from the tables to be joined/merged with another one.
• Apply FULL OUTER JOIN containing the second table and ON condition containing matched columns referencing their respective tables:

Example 1: Use FULL OUTER JOIN to Combine Tables

The following query is used to fetch the data from the vehicle table to start the first example of FULL OUTER JOIN:

SELECT * FROM vehicle;

Running the above query will display all records of the vehicle table:

Use this query to get the data of the person table:

SELECT * FROM person;

It will display the results of data present in the person table:

Use the following query to apply FULL OUTER JOIN on the vehicle and person tables:

SELECT * FROM person p
FULL OUTER JOIN vehicle v 
 ON v.id = p.id;

The above code contains:

• It selects all records from the person table and applies FULL OUTER JOIN with the vehicle table.
• It uses id columns from vehicle and person tables with the ON condition:

Example 2: Use FULL OUTER JOIN With WHERE Clause

Use the following query to apply FULL OUTER JOIN based on the condition specified in the WHERE clause:

SELECT * FROM person p
FULL OUTER JOIN vehicle v 
 ON v.id = p.id
WHERE vehicle_id IS NULL;

Here in the above code block:

• It uses the id column from person and vehicle tables to apply FULL OUTER JOIN on them
• The additional condition uses the WHERE clause to find a specific record that does not contain any value in the vehicle_id column:

That’s all about the use of FULL OUTER JOIN in PostgreSQL.

Conclusion

In PostgreSQL, FULL OUTER JOIN combines the working of LEFT and RIGHT JOINs to join multiple tables on the database. It generates all the data matched and unmatched from the tables involved in the joining query having a common column between them. The ON condition is used with FULL OUTER JOIN containing common columns from the tables and the WHERE clause can add conditions to it. This guide has explained the use of FULL OUTER JOIN in PostgreSQL.