PostgreSQL FULL OUTER JOIN (original) (raw)

Last Updated : 12 Jul, 2025

In PostgreSQL, the **FULL OUTER JOIN is a **powerful feature that combines the effects of both **LEFT JOIN and **RIGHT JOIN. This join operation retrieves **all rows from both tables involved in the join, including **unmatched rows from each table. For any unmatched rows, PostgreSQL fills the result with **NULL values for the columns of the table lacking a match.

In this article, we will explain the **syntax and practical use cases of **FULL OUTER JOIN, providing examples with **detailed outputs. By the end, we will have a **complete understanding of how to use FULL OUTER JOIN effectively in our **PostgreSQL database queries to generate understanding and **comprehensive **reports.

PostgreSQL FULL OUTER JOIN

This feature is essential for scenarios where both **matching and **non-matching data are required, such as in **data integration, **comparative analysis, or **comprehensive reporting. FULL OUTER JOIN allows us to bring together data from two sources without losing any information, even if there are gaps in **matching records.

**Syntax

SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1
FULL JOIN table2 ON table1.matching_column = table2.matching_column;

Or alternatively:

SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1
FULL OUTER JOIN table2 ON table1.matching_column = table2.matching_column;

**Key Terms

• table1 : The first table in the join operation.
• table2 : The second table in the join operation.
• matching_column : The column common to both tables used to match rows.

Venn Diagram for FULL OUTER JOIN

The Venn diagram for FULL OUTER JOIN is given below:

PostgreSQL FULL OUTER JOIN Examples

Let us take a look at some of the examples of FULL OUTER JOIN in PostgreSQL to better understand the concept. In this article, we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

**Example 1: Films and Actors Query

In this example, we’ll fetch details of **all films along with their **actors. We’ll use the **film table and **actor table to retrieve data, including any records that don’t have a **matching pair in the other table.

**Query:

SELECT
title,
first_name,
last_name
FROM
film f
FULL OUTER JOIN actor a ON a.actor_id = f.film_id;

**Output

**Explanation:

• This query retrieves all **films (film table) and **all actors (actor table).
• If a film doesn’t have an actor associated, the actor_first_name and actor_last_name columns will contain NULL.
• Similarly, if an actor isn’t associated with any film, the film_title column will contain NULL.

**Example 2: Films and Language Query

Here we will make a query for all the films and the language of the movie using the "**film" table and "**language" table from our sample database.

**Query:

SELECT
title,
name
FROM
film f
FULL OUTER JOIN language l ON l.language_id = f.film_id;

**Output

**Explanation:

• This query retrieves all records from the film table with their respective languages from the language table.
• If a film does not have an associated language, language_name will be NULL.
• If a language does not have any films associated with it, film_title will be NULL.

Important Points About FULL OUTER JOIN in PostgreSQL

• **FULL OUTER JOIN in PostgreSQL combines the results of both **LEFT JOIN and **RIGHT JOIN.
• Since **FULL OUTER JOIN includes unmatched rows with **NULL values, it's essential to handle NULL values appropriately in subsequent data processing steps, such as using **COALESCE()or **CASE**statements to manage **NULL values based on specific requirements.
• FULL OUTER JOINs can be more resource-intensive compared to INNER JOINs or other types of joins, especially with large datasets.
• Common applications of **FULL OUTER JOIN include data integration tasks, comparing datasets, and generating comprehensive reports.

Conclusion

In PostgreSQL, the **FULL OUTER JOIN is an essential tool for cases where we need a complete view of two tables, including both matching and non-matching records. By using FULL OUTER JOIN, we ensure that no data is left out, which is particularly useful in **data integration, **report generation, and **comparative analysis.

However, because **FULL OUTER JOIN can be **resource-intensive, it's crucial to use it selectively and optimize queries for large datasets. By handling **NULLs effectively and **monitoring performance, we can use FULL OUTER JOIN to create insightful and comprehensive reports that bring together diverse data sources in **PostgreSQL.