PostgreSQL: Performing Full Outer Joins (FULL JOIN)

Combine data from two tables using PostgreSQL's FULL OUTER JOIN. This guide explains the syntax and usage of FULL JOIN.

Performing Full Outer Joins in PostgreSQL

Introduction

In PostgreSQL, a `FULL OUTER JOIN` (or simply `FULL JOIN`) combines the results of a `LEFT JOIN` and a `RIGHT JOIN`. It returns all rows from both tables involved in the join. If there's a match between rows in both tables, the columns from both rows are included in the result. If there's no match for a row in one table, the result includes that row with NULL values for the columns from the other table.

Understanding `FULL OUTER JOIN`

A `FULL OUTER JOIN` aims to include all rows from both participating tables. It's particularly useful when you need to see all data from both tables, even if there are no corresponding matches in the other table.

`FULL OUTER JOIN` Syntax

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

The `OUTER` keyword is optional.

Example: Joining `Summer_fruits` and `Winter_fruits`

Let's create and populate two tables, then perform a `FULL JOIN`:

CREATE TABLE Summer_fruits (
    SF_ID INT PRIMARY KEY,
    Summer_fruits_name VARCHAR(250) NOT NULL
);

CREATE TABLE Winter_fruits (
    WF_ID INT PRIMARY KEY,
    Winter_fruits_name VARCHAR(250) NOT NULL
);

--Insert statements for both tables would go here.

(Example showing the `SELECT` statements to view data in `Summer_fruits` and `Winter_fruits` tables would be included here.)

SELECT SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits
FULL JOIN Winter_fruits ON SF_ID = WF_ID;

(Example showing the output of the `FULL JOIN` query would be included here.)

Using Table Aliases

Table aliases make queries more concise and readable:

SELECT s.SF_ID, s.Summer_fruits_name, w.WF_ID, w.Winter_fruits_name
FROM Summer_fruits s
FULL JOIN Winter_fruits w ON s.SF_ID = w.WF_ID;

(Example showing the output of the `FULL JOIN` query with aliases would be included here.)

`FULL JOIN` with a `WHERE` Clause

You can add a `WHERE` clause to filter the results of a `FULL JOIN`:

SELECT SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits
FULL JOIN Winter_fruits ON SF_ID = WF_ID
WHERE Summer_fruits_name != 'Mango';

(Example showing the output of the `FULL JOIN` query with a `WHERE` clause would be included here.)

Conclusion

The `FULL OUTER JOIN` is a powerful tool in PostgreSQL for retrieving data from multiple tables, ensuring that all rows from both tables are included in the result, regardless of whether there are matching rows in the other table. Using aliases and `WHERE` clauses enhances the flexibility and readability of your queries.