Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablea(Acol1 int, Acol2 int);
CREATE TABLE tableb(Bcol1 int, Bcol2 int);
CREATE TABLE tablec(Ccol1 int, Ccol2 int);
-- Here we see "Merge Full Join":
EXPLAIN
SELECT * FROM tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1;

QUERY PLAN

Merge Full Join  (cost=317.01..711.38 rows=25538 width=16)

  Merge Cond: (b.bcol1 = c.ccol1)

  ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

        Sort Key: b.bcol1

        ->  Seq Scan on tableb b  (cost=0.00..32.60 rows=2260 width=8)

  ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

        Sort Key: c.ccol1

        ->  Seq Scan on tablec c  (cost=0.00..32.60 rows=2260 width=8)

-- Query planner optimizes to "Merge Left Join":
EXPLAIN
SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2
FROM   tableA
LEFT   JOIN (tableB FULL JOIN tableC ON Bcol1 = Ccol1) ON Acol1 = Bcol1;

QUERY PLAN

Merge Left Join  (cost=475.52..5209.87 rows=288579 width=24)

  Merge Cond: (tablea.acol1 = tableb.bcol1)

  ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

        Sort Key: tablea.acol1

        ->  Seq Scan on tablea  (cost=0.00..32.60 rows=2260 width=8)

  ->  Materialize  (cost=317.01..775.23 rows=25538 width=16)

        ->  Merge Left Join  (cost=317.01..711.38 rows=25538 width=16)

              Merge Cond: (tableb.bcol1 = tablec.ccol1)

              ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                    Sort Key: tableb.bcol1

                    ->  Seq Scan on tableb  (cost=0.00..32.60 rows=2260 width=8)

              ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                    Sort Key: tablec.ccol1

                    ->  Seq Scan on tablec  (cost=0.00..32.60 rows=2260 width=8)

-- Introduce optimization barrier with OFFSET 0 hack -> "Merge Full Join":
EXPLAIN
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   OFFSET 0  -- undocumented hack
   ) d ON a.Acol1 = d.Bcol1;

QUERY PLAN

Merge Left Join  (cost=2994.69..7334.68 rows=288579 width=24)

  Merge Cond: (a.acol1 = b.bcol1)

  ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

        Sort Key: a.acol1

        ->  Seq Scan on tablea a  (cost=0.00..32.60 rows=2260 width=8)

  ->  Sort  (cost=2836.19..2900.03 rows=25538 width=16)

        Sort Key: b.bcol1

        ->  Merge Full Join  (cost=317.01..711.38 rows=25538 width=16)

              Merge Cond: (b.bcol1 = c.ccol1)

              ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                    Sort Key: b.bcol1

                    ->  Seq Scan on tableb b  (cost=0.00..32.60 rows=2260 width=8)

              ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                    Sort Key: c.ccol1

                    ->  Seq Scan on tablec c  (cost=0.00..32.60 rows=2260 width=8)

-- Introduce optimization barrier with CTE -> "Merge Full Join":
EXPLAIN
WITH cte AS MATERIALIZED ( -- requires "AS MATERIALIZED" in Postgres 12 or later!
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   ) 
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN cte d ON a.Acol1 = d.Bcol1;

QUERY PLAN

Merge Left Join  (cost=3250.07..7590.06 rows=288579 width=24)

  Merge Cond: (a.acol1 = d.bcol1)

  CTE cte

    ->  Merge Full Join  (cost=317.01..711.38 rows=25538 width=16)

          Merge Cond: (b.bcol1 = c.ccol1)

          ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                Sort Key: b.bcol1

                ->  Seq Scan on tableb b  (cost=0.00..32.60 rows=2260 width=8)

          ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

                Sort Key: c.ccol1

                ->  Seq Scan on tablec c  (cost=0.00..32.60 rows=2260 width=8)

  ->  Sort  (cost=158.51..164.16 rows=2260 width=8)

        Sort Key: a.acol1

        ->  Seq Scan on tablea a  (cost=0.00..32.60 rows=2260 width=8)

  ->  Sort  (cost=2380.19..2444.03 rows=25538 width=16)

        Sort Key: d.bcol1

        ->  CTE Scan on cte d  (cost=0.00..510.76 rows=25538 width=16)