PostgreSQL Full Join

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

Introduction to PostgreSQL FULL JOIN statement #

The FULL JOIN merges rows from two tables and returns all rows from both tables. Additionally, the FULL JOIN uses NULLs for every column of the table that does not have a matching row.

In other words, the FULL JOIN combines the result sets of the LEFT JOIN and RIGHT JOIN.

Here’s the syntax of the FULL JOIN table:

SELECT
  table1.column1,
  table2.column2,
  ...
FROM
  table1
  FULL JOIN table2 ON table2.column1 = table1.column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the first table (table1) in the FROM clause.
• Second, provide the name of the second table (table2) you want to perform a full join in the FULL JOIN clause.
• Third, use a condition to match rows from both tables in the ON clause. The condition matches rows by comparing the values of column1 in table1 with the values of column1 in table2.
• Finally, specify the columns you want to retrieve data in the SELECT clause.

The FULL OUTER JOIN is an alternative syntax of the FULL JOIN:

SELECT
  table1.column1,
  table2.column2,
  ...
FROM
  table1
  FULL OUTER JOIN table2 ON table2.column1 = table1.column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the OUTER keyword is optional.

How the PostgreSQL FULL JOIN works #

First, the FROM clause returns all rows from the table1.

Second, the FULL JOIN matches each row in the table1 with every row in the table2 by comparing values from column1 in both tables.

If they match, PostgreSQL merges two rows into one row. Otherwise, it fills NULLs for the columns of the row from table that has no match and merges with the row from the other table.

Third, return a result set that includes columns specified in the SELECT clause.

Setting up sample tables #

First, create a new table called warehouses with some sample data:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert three rows into the brands table:

INSERT INTO
  brands (name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 brand_id |  name
----------+---------
        1 | Apple
        2 | Samsung
        3 | GoogleCode language: plaintext (plaintext)

Third, create a new table called products:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, insert four rows into products table:

INSERT INTO
  products (name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2),
  ('Oppo Find Flip', 499.99, NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 product_id |       name       |  price  | brand_id
------------+------------------+---------+----------
          1 | iPhone 14 Pro    |  999.99 |        1
          2 | iPhone 15 Pro    | 1199.99 |        1
          3 | Galaxy S23 Ultra | 1149.47 |        2
          4 | Oppo Find Flip   |  499.99 |     NULLCode language: plaintext (plaintext)

PostgreSQL FULL JOIN example #

The following statement uses the FULL JOIN to merge rows from the products and brands tables:

SELECT
  p.name as product_name,
  p.price,
  b.name brand_name
FROM
  products p
  FULL JOIN brands b ON b.brand_id = p.brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   |  price  | brand_name
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 Oppo Find Flip   |  499.99 | NULL
 NULL             |    NULL | GoogleCode language: plaintext (plaintext)

Try it

How it works.

First, the FROM clause returns all rows from the products table.

Second, the FULL JOIN clause matches each row from the products table with every row from the brands table by comparing the values in the brand_id column.

If there is a match, the FULL JOIN merges the rows from both tables into a single row.

If there is no match, the FULL JOIN fills every column of the row in the table that has no match with NULLs and merges with the row from the other table.

The row with product_id 1 from the products table matches the row with brand_id 1 from the brands table:

The row with product_id 2 from the products table matches the row with brand_id 1 from the brands table:

The row with product_id 3 from the products table matches the row with brand_id 2 from the brands table:

The row with product_id 4 from the products table does not have any matching rows in the brands table, the FULL JOIN fills the brand_id and name columns from the brands table with NULL:

The row with brand_id 3 from the brands table does not have any matching rows in the products table, the FULL JOIN fills the columns of the row from the products table with NULL:

Third, return the columns specified in the SELECT clause, also assign the column alias to these columns:

Summary #

• Use PostgreSQL FULL JOIN to merge rows from two tables and return rows from both tables including matching and non-matching rows.
• FULL OUTER JOIN is an alternative syntax of the FULL JOIN.

Quiz #