Full Data Merge with FULL OUTER JOIN

Today we're gonna dive into the most democratic way to join data — FULL OUTER JOIN. This is the join where everyone gets in the result, even if they don't have a pair.

FULL OUTER JOIN is a type of data join where all rows from both tables are returned. If a row in one table doesn't have a matching row in the other, the missing values in the result set are filled with NULL. It's like keeping track of everyone who showed up at two different parties: even if someone only came to one, they're still counted.

You can picture it like this:

Table A                     Table B
+----+----------+             +----+----------+
| id | name     |             | id | course   |
+----+----------+             +----+----------+
| 1  | Alice    |             | 2  | Math     |
| 2  | Bob      |             | 3  | Physics  |
| 4  | Charlie  |             | 5  | History  |
+----+----------+             +----+----------+

FULL OUTER JOIN RESULT:
+----+----------+----------+
| id | name     | course   |
+----+----------+----------+
| 1  | Alice    | NULL     |
| 2  | Bob      | Math     |
| 3  | NULL     | Physics  |
| 4  | Charlie  | NULL     |
| 5  | NULL     | History  |
+----+----------+----------+

Rows without a match are kept, but the data for missing columns will be filled with NULL.

Syntax of FULL OUTER JOIN

The syntax is simple, but it's super powerful:

SELECT
    columns
FROM 
    table1
FULL OUTER JOIN 
    table2
ON table1.common_column = table2.common_column;

The key part here is FULL OUTER JOIN, which tells PostgreSQL to grab all rows from both tables. If a row doesn't have a match based on the ON condition, the values are replaced with NULL.

Usage Examples

Let's break down some real examples using the familiar university database with students and enrollments tables.

Example 1: List of all students and courses

Imagine we have two tables:

Table students:

Table enrollments:

Our goal is to make a complete list of students and courses, including students who aren't enrolled in any course and courses with no students.

Here's the query:

SELECT
    s.student_id, 
    s.name, 
    e.course
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Result:

As you can see, all students and all courses are in the result. Student Charlie isn't enrolled in any courses, so his course field is NULL. And the course History doesn't have a student, so its student_id and name are NULL.

Example 2: Sales and Products Analysis

Now let's think about a store. We have two tables:

Table products:

Table sales:

We want to get a full list of all products and sales, including products that weren't sold and sales with invalid product_ids.

Query:

SELECT
    p.product_id,
    p.name AS product_name,
    s.quantity
FROM 
    products p
FULL OUTER JOIN 
    sales s
ON 
    p.product_id = s.product_id;

Result:

Here we see that Smartphone had no sales (quantity = NULL), and the sale with product_id = 4 doesn't match any product.

Practice Task

Try to write a query for the departments and employees tables:

Table departments:

Table employees:

Write a FULL OUTER JOIN to get a complete list of departments and employees. Fill in missing data with NULL rows.

How to Handle NULL Values

The NULL value problem is an unavoidable side effect of using FULL OUTER JOIN. For example, in real-world tasks you might want to replace NULL with something more meaningful. In PostgreSQL you can do this with the COALESCE() function.

Example:

SELECT
    COALESCE(s.name, 'No Student') AS student_name,
    COALESCE(e.course, 'No Course') AS course_name
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Result:

Now instead of NULL we see clear values that make reports easier to read.

When to Use FULL OUTER JOIN

FULL OUTER JOIN is useful when you need to see all data from both tables, even if they're not fully connected. Examples:

• Sales and product reports — to see both sold and unsold products.
• Student and course analysis — to check if there are any missing data.
• List comparison — for example, to find mismatches between two data sets.

Hope this lecture gave you a solid idea of FULL OUTER JOIN. Now you're ready for the awesome world of more complex joins and data processing!