# OUTER JOIN

> For the complete documentation index, see [llms.txt](https://docs.redpanda.com/llms.txt). Component-specific: [cloud-data-platform-full.txt](https://docs.redpanda.com/cloud-data-platform-full.txt)

---
title: OUTER JOIN
latest-operator-version: v26.1.4
latest-console-tag: v3.7.3
latest-connect-version: 4.93.0
latest-redpanda-tag: v26.1.9
docname: sql/sql-clauses/from/outer-join
page-component-name: cloud-data-platform
page-version: master
page-component-version: master
page-component-title: Cloud
page-relative-src-path: sql/sql-clauses/from/outer-join.adoc
page-edit-url: https://github.com/redpanda-data/cloud-docs/edit/main/modules/reference/pages/sql/sql-clauses/from/outer-join.adoc
description: The OUTER JOIN returns all the records from the selected fields between two tables, whether the join condition is met or not.
page-topic-type: reference
page-git-created-date: "2026-05-26"
page-git-modified-date: "2026-05-26"
---

<!-- Source: https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-clauses/from/outer-join.md -->

The `OUTER JOIN` (also known as `FULL OUTER JOIN`) returns all the records from the selected fields between the two tables (left and right), whether the join condition is met or not.

## [](#inner-join-vs-outer-join)Inner join vs. outer join

An `INNER JOIN` returns only matched rows. An `OUTER JOIN` returns all rows from both tables, whether matched or not.

## [](#syntax)Syntax

```sql
SELECT column_1, column_2...
FROM table_1
FULL OUTER JOIN table_2
ON table_1.matching_field = table_2.matching_field;
```

In this syntax:

1.  `SELECT column_1, column_2…​` defines the columns from both tables to display data.

2.  `FROM table_1` represents `table_1` as the left table in the `FROM` clause.

3.  `FULL OUTER JOIN table_2` represents `table_2` as the right table in the `FULL OUTER JOIN` condition.

4.  `ON table_1.matching_field = table_2.matching_field` sets the join condition after the `ON` keyword with the matching field between the two tables.


### [](#table-alias)Table alias

A table alias is a temporary name given to a table in a query. Aliases don’t change the result; they make queries easier to read.

```sql
SELECT A.column_1, B.column_2...
FROM table_1 A
FULL OUTER JOIN table_2 B
ON A.matching_field = B.matching_field;
```

> 📝 **NOTE**
>
> If there are no matched records from the joined tables, `NULL` values are returned in every column of the table that does not have the matching record.

## [](#examples)Examples

Create the `departments` table:

```sql
CREATE TABLE departments (
    department_id int,
    department_name text
);
INSERT INTO departments (department_id,department_name)
VALUES
    (1001, 'Sales'),
    (1002, 'Marketing'),
    (1003, 'HR'),
    (1004, 'Project'),
    (1005, 'Product');
```

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

This returns:

```sql
+----------------+------------------+
| department_id  | department_name  |
+----------------+------------------+
| 1001           | Sales            |
| 1002           | Marketing        |
| 1003           | HR               |
| 1004           | Project          |
| 1005           | Product          |
+----------------+------------------+
```

Create the `employee` table:

```sql
CREATE TABLE employee (
    employee_id int,
    employee_name text,
    dept_id int
);
INSERT INTO employee (
    employee_id,
    employee_name,
    dept_id
)
VALUES
    (2001,'Tony Stark', 1002),
    (2002,'Christian Bale', 1002),
    (2003,'Anne Hailey', 1003),
    (2004,'Wilson Cliff', 1004),
    (2005,'Susan Oh', 1001),
    (2006,'Julian Robert', 1001),
    (2007,'Gilbert Tom', null);
```

```sql
SELECT * FROM employee;
```

This returns:

```sql
+--------------+-------------------+------------+
| employee_id  | employee_name     | dept_id    |
+--------------+-------------------+------------+
| 2001         | Tony Stark        | 1002       |
| 2002         | Christian Bale    | 1002       |
| 2003         | Anne Hailey       | 1003       |
| 2004         | Wilson Cliff      | 1004       |
| 2005         | Susan Oh          | 1001       |
| 2006         | Julian Robert     | 1001       |
| 2007         | Gilbert Tom       | null       |
+--------------+-------------------+------------+
```

### [](#full-outer-join)FULL OUTER JOIN

A `FULL OUTER JOIN` query against these tables:

```sql
SELECT employee_name, department_name
FROM departments
FULL OUTER JOIN employee
ON departments.department_id = employee.dept_id;
```

The result shows every department with an employee, the employees who work under a specific department, every department that does not have any employees, and the employees who do not belong to a specific department.

```sql
+-------------------+-------------------+
| employee_name     | department_name   |
+-------------------+-------------------+
| Julian Robert     | Sales             |
| Susan Oh          | Sales             |
| Christian Bale    | Marketing         |
| Tony Stark        | Marketing         |
| Anne Hailey       | HR                |
| Wilson Cliff      | Project           |
| Gilbert Tom       | null              |
| null              | Product           |
+-------------------+-------------------+
```

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

![Venn diagram showing a full outer join between the Departments and Employee tables](https://docs.redpanda.com/cloud-data-platform/reference/_images/sql/outer-join-venn.png)

### [](#full-outer-join-with-where-clause)FULL OUTER JOIN with WHERE clause

#### [](#find-departments-with-no-employees)Find departments with no employees

1.  To find departments that do not have any employees, add a `WHERE` clause with `NULL`:

    ```sql
    SELECT employee_name, department_name
    FROM departments
    FULL OUTER JOIN employee
    ON departments.department_id = employee.dept_id
    WHERE employee_name IS NULL;
    ```

2.  The query returns:

    ```sql
    +------------------+--------------------+
    | employee_name    | department_name    |
    +------------------+--------------------+
    | null             | Product            |
    +------------------+--------------------+
    ```


The result indicates that the `Product` department has no employees.

#### [](#find-employees-with-no-department)Find employees with no department

1.  To find employees who do not belong to any department, add a `WHERE` clause with `NULL`:

    ```sql
    SELECT employee_name, department_name
    FROM employee
    FULL OUTER JOIN departments
    ON employee.dept_id = departments.department_id
    WHERE department_name IS NULL;
    ```

2.  The query returns:

    ```sql
    +------------------+--------------------+
    | employee_name    | department_name    |
    +------------------+--------------------+
    | Gilbert Tom      | null               |
    +------------------+--------------------+
    ```


The result shows that Gilbert Tom does not belong to any department.

The following Venn diagram illustrates the `FULL OUTER JOIN` with `WHERE` clause filtering for NULL values:

![Venn diagram showing a full outer join with WHERE clause filtering for NULL values](https://docs.redpanda.com/cloud-data-platform/reference/_images/sql/outer-join-where-venn.png)