Open In App

PostgreSQL – FULL OUTER JOIN

Improve
Improve
Like Article
Like
Save
Share
Report

The PostgreSQL FULL OUTER JOIN or FULL JOIN creates the result-set by combining the result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

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

or,

SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 
FULL OUTER JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.

The Venn diagram for FULL OUTER JOIN is given below:

For the sake of 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:

Here we will make a query for all the films and the actors of the movie using the “film” table and “actor” table from our sample database.

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

Output:

Example 2:
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.

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

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads