PostgreSQL – FULL OUTER JOIN
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: