Join types

Contents

Join types#

At this page we’ll consider different join types and their features in clickhouse and postgres.

docker run -d --rm\
    --name jointypes_example_postgres\
    -e POSTGRES_PASSWORD=postgres\
    postgres:15.4 &> /dev/null
docker run -d --rm\
    --name jointypes_example_click\
    clickhouse/clickhouse-server:24
sleep 5

4ac26d66b863b8203effa902c975e462493dab89ad5d3b1fd3527bad50508105

Note don’t forget to clean the environment after you try everything you wanted.

docker stop jointypes_example_postgres jointypes_example_click

jointypes_example_postgres
jointypes_example_click

Full join#

A FULL JOIN combines all rows from both tables being joined, including those without matching values in the specified join columns. If a row from either table does not have a corresponding match, the result will contain NULL values in the columns of the non-matching table.

The following cell demonstrates the application of the FULL JOIN in postgres.

docker exec -i jointypes_example_postgres psql -U postgres -d postgres << EOF

WITH 
table1(col1) AS (VALUES (1), (2), (3), (4)),
table2(col1) AS (VALUES (1), (3), (8), (9))

SELECT *
FROM 
    table1
    FULL JOIN table2 ON table1.col1=table2.col1
;
EOF

 col1 | col1 
------+------
    1 |    1
    2 |     
    3 |    3
    4 |     
      |    8
      |    9
(6 rows)

The following cell demonstrates the same example in ClickHouse; the result is nearly identical, with the only difference being that it uses zeros instead of NULLs for non-matching rows.

docker exec jointypes_example_click clickhouse-client -q "
CREATE TEMPORARY TABLE table1(val Int32) ENGINE = Memory;
CREATE TEMPORARY TABLE table2(val Int32) ENGINE = Memory;

INSERT INTO table1 VALUES
    (1), (2), (3), (4);
INSERT INTO table2 VALUES
    (1), (3), (8), (9);

SELECT table1.val, table2.val
    FROM table1 
    FULL JOIN table2 ON table1.val=table2.val
FORMAT PRETTY;
"

   ┏━━━━━┳━━━━━━━━━━━━┓
   ┃ val ┃ table2.val ┃
   ┡━━━━━╇━━━━━━━━━━━━┩
1. │   1 │          1 │
   ├─────┼────────────┤
2. │   2 │          0 │
   ├─────┼────────────┤
3. │   3 │          3 │
   ├─────┼────────────┤
4. │   4 │          0 │
   └─────┴────────────┘
   ┏━━━━━┳━━━━━━━━━━━━┓
   ┃ val ┃ table2.val ┃
   ┡━━━━━╇━━━━━━━━━━━━┩
5. │   0 │          9 │
   ├─────┼────────────┤
6. │   0 │          8 │
   └─────┴────────────┘
Contents