Oracle FULL OUTER JOIN

Summary: in this tutorial, you will learn how to use the Oracle FULL OUTER JOIN to merge rows from two tables.

Introduction to Oracle FULL OUTER JOIN clause #

The FULL OUTER JOIN is an optional clause of the SELECT statement. The FULL OUTER JOIN clause allows you to merge rows from two tables.

Here’s the syntax of the FULL OUTER JOIN in Oracle:

SELECT
  column_list
FROM
  X
  FULL OUTER JOIN Y ON X.id = Y.id;Code language: SQL (Structured Query Language) (sql)

The FULL OUTER JOIN clause returns all rows from both tables X and Y.

If a row in a table does not have a matching row in another, the FULL OUTER JOIN clause uses NULLs for every column of the table that has no matching row.

In other words, a FULL OUTER JOIN returns a result set that is the combination of the results of a LEFT JOIN and RIGHT JOIN.

The OUTER keyword is optional. Therefore, the FULL OUTER JOIN and FULL JOIN are equivalent.

Visualizing Oracle FULL OUTER JOIN #

Suppose you have two tables X and Y, and want to merge rows from both tables using a full outer join:

• The X table has two columns: id (key) and x.
• The Y table has two columns: id (key) and y.

The FULL OUTER JOIN returns a result set that includes rows from both tables whether or not the rows have matching rows from another table. It fills NULLs for columns of rows in the table that do not have matching rows in another table:

Alternatively, the following Venn diagram depicts how an Oracle FULL OUTER JOIN works:

Oracle FULL OUTER JOIN examples #

First, create two tables members and projects. Suppose each member can join zero or one project, and each project can have zero or more members:

CREATE TABLE projects (
  project_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_name VARCHAR2 (100) NOT NULL
);

CREATE TABLE members (
  member_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_name VARCHAR2 (100) NOT NULL,
  project_id INT,
  FOREIGN KEY (project_id) REFERENCES projects (project_id)
);Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert some projects and members into the projects and members tables:

INSERT INTO
  projects (project_name)
VALUES
  ('ERP');

INSERT INTO
  projects (project_name)
VALUES
  ('Sales CRM');

INSERT INTO
  members (member_name, project_id)
VALUES
  ('John Doe', 1);

INSERT INTO
  members (member_name, project_id)
VALUES
  ('Jane Doe', 1);

INSERT INTO
  members (member_name, project_id)
VALUES
  ('Jack Daniel', NULL);Code language: SQL (Structured Query Language) (sql)

Try it

Third, use the full outer join to query data from members and projects tables:

SELECT 
    member_name, 
    project_name
FROM 
    members m
FULL OUTER JOIN projects p ON p.project_id = m.project_id
ORDER BY 
    member_name;  
Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Jack Daniel does not join any project; Jane Doe and John Doe join the ERP project, and the Sales CRM project has no members.

To find the project that does not have any members, you use the following query:

SELECT
  project_name,
  member_name
FROM
  members m
  FULL OUTER JOIN projects p ON p.project_id = m.project_id
WHERE
  member_name IS NULL
ORDER BY
  member_name;Code language: SQL (Structured Query Language) (sql)

Try it

Similarly, you can find members who do not participate in any project by using the following query:

SELECT
  member_name,
  project_name
FROM
  members m
  FULL OUTER JOIN projects p ON p.project_id = m.project_id
WHERE
  project_name IS NULL
ORDER BY
  member_name;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

• Use the Oracle FULL OUTER JOIN clause to merge rows from two tables and select all rows from both.

Quiz #