> This page location: Join Tables > FULL OUTER JOIN
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL FULL OUTER JOIN

**Info:** FULL OUTER JOIN is standard PostgreSQL and works the same on any Postgres deployment, so you can apply these patterns wherever you run your database. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers the performance, security, and native Lakehouse integration you need. If you're a developer or startup that needs to ship and scale quickly, [Neon](https://neon.com) gives you the fastest path from idea to production on Postgres.

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `FULL OUTER JOIN` to query data from two tables.

## Introduction to the PostgreSQL FULL OUTER JOIN clause

The `FULL OUTER JOIN` combine data from two tables and returns all rows from both tables, including matching and non-matching rows from both sides.

In other words, the `FULL OUTER JOIN` combines the results of both the [left join](https://neon.com/postgresql/tutorial/postgresql-left-join) and the [right join](https://neon.com/postgresql/tutorial/postgresql-right-join).

Here's the basic syntax of `FULL OUTER JOIN` clause:

```sql
SELECT select_list
FROM table1
FULL OUTER JOIN table2
   ON table1.column_name = table2.column_name;
```

In this syntax:

- First, specify the columns from `table1` and `table2` in the `select_list`.
- Second, specify the `table1` that you want to retrieve data in the `FROM` clause.
- Third, specify the `table2` that you want to join with the `table1` in the `FULL OUTER JOIN` clause.
- Finally, define a condition for joining two tables.

The `FULL OUTER JOIN` is also known as `FULL JOIN`. The `OUTER` keyword is optional.

### How the FULL OUTER JOIN works

**Step 1. Initialize the result set:**

- The `FULL OUTER JOIN` starts with an empty result set.

**Step 2. Match rows:**

- First, identify rows in `table1` and `table2` where the values in the specified `column_name` match.
- Then, include these matching rows in the result set.

**Step 3. Include non-matching rows from the `table1` and `table2`:**

- First, include rows from `table1` that do not have a match in `table2`. For the columns from `table2` in these rows, include NULLs.
- Second, include rows from `table2` that do not have a match in `table1`. For the columns from `table1` in these rows, include NULLs.

**Step 4. Return the result set:**

- Return the final result set will contain all rows from both tables, with matching rows and non-matching rows from both `table1` and `table2`.
- If a row has a match on both sides, combine the values into a single row.
- If there is no match on one side, the columns from the non-matching side will have NULLs.

The following Venn diagram illustrates the `FULL OUTER JOIN` operation:

![PostgreSQL Join - Full Outer Join](https://neon.com/postgresqltutorial/PostgreSQL-Join-Full-Outer-Join.png)

## Setting up sample tables

First, [create two new tables](https://neon.com/postgresql/tutorial/postgresql-create-table) for the demonstration: `employees` and `departments`:

```sql
CREATE TABLE departments (
  department_id serial PRIMARY KEY,
  department_name VARCHAR (255) NOT NULL
);
CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  employee_name VARCHAR (255),
  department_id INTEGER
);
```

Each department has zero or many employees and each employee belongs to zero or one department.

Second, insert some sample data into the `departments` and `employees` tables.

```sql
INSERT INTO departments (department_name)
VALUES
  ('Sales'),
  ('Marketing'),
  ('HR'),
  ('IT'),
  ('Production');
INSERT INTO employees (employee_name, department_id)
VALUES
  ('Bette Nicholson', 1),
  ('Christian Gable', 1),
  ('Joe Swank', 2),
  ('Fred Costner', 3),
  ('Sandra Kilmer', 4),
  ('Julia Mcqueen', NULL);

```

Third, query data from the `departments` and `employees` tables:

```sql
SELECT * FROM departments;
```

Output:

```text
 department_id | department_name
---------------+-----------------
             1 | Sales
             2 | Marketing
             3 | HR
             4 | IT
             5 | Production
(5 rows)
```

```sql
SELECT * FROM employees;
```

Output:

```text
 employee_id |  employee_name  | department_id
-------------+-----------------+---------------
           1 | Bette Nicholson |             1
           2 | Christian Gable |             1
           3 | Joe Swank       |             2
           4 | Fred Costner    |             3
           5 | Sandra Kilmer   |             4
           6 | Julia Mcqueen   |          null
(6 rows)
```

## PostgreSQL FULL OUTER JOIN examples

Let's take some examples of using the `FULL OUTER JOIN` clause.

### 1) Basic FULL OUTER JOIN example

The following query uses the `FULL OUTER JOIN` to query data from both `employees` and `departments` tables:

```sql
SELECT
  employee_name,
  department_name
FROM
  employees e
FULL OUTER JOIN departments d
  ON d.department_id = e.department_id;
```

Output:

```text
  employee_name  | department_name
-----------------+-----------------
 Bette Nicholson | Sales
 Christian Gable | Sales
 Joe Swank       | Marketing
 Fred Costner    | HR
 Sandra Kilmer   | IT
 Julia Mcqueen   | null
 null            | Production
(7 rows)
```

The result set includes every employee who belongs to a department and every department which have an employee.

Additionally, it includes every employee who does not belong to a department and every department that does not have an employee.

### 2) Using FULL OUTER JOIN with WHERE clause example

The following example use the `FULL OUTER JOIN` with a [WHERE](https://neon.com/postgresql/tutorial/postgresql-where) clause to find the department that does not have any employees:

```sql
SELECT
  employee_name,
  department_name
FROM
  employees e
FULL OUTER JOIN departments d
  ON d.department_id = e.department_id
WHERE
  employee_name IS NULL;

```

Output:

```text
 employee_name | department_name
---------------+-----------------
 null          | Production
(1 row)
```

The result shows that the `Production` department does not have any employees.

The following example use the `FULL OUTER JOIN` clause with a `WHERE` clause to find employees who do not belong to any department:

```sql
SELECT
  employee_name,
  department_name
FROM
  employees e
FULL OUTER JOIN departments d
  ON d.department_id = e.department_id
WHERE
  department_name IS NULL;
```

Output:

```
 employee_name | department_name
---------------+-----------------
 Julia Mcqueen | null
(1 row)
```

The output shows that `Julia Mcqueen` does not belong to any department.

## Summary

- Use the PostgreSQL `FULL OUTER JOIN` clause to combine data from both tables, ensuring that matching rows are included from both the left and right tables, as well as unmatched rows from either table.

---

## Related docs (Join Tables)

- [Joins](https://neon.com/postgresql/tutorial/joins)
- [Table Aliases](https://neon.com/postgresql/tutorial/alias)
- [INNER JOIN](https://neon.com/postgresql/tutorial/inner-join)
- [LEFT JOIN](https://neon.com/postgresql/tutorial/left-join)
- [RIGHT JOIN](https://neon.com/postgresql/tutorial/right-join)
- [SELF-JOIN](https://neon.com/postgresql/tutorial/self-join)
- [Cross Join](https://neon.com/postgresql/tutorial/cross-join)
- [Natural Join](https://neon.com/postgresql/tutorial/natural-join)