Introducing UNION, UNION ALL, and UNION DISTINCT in ClickHouse with Practical Examples

  

ClickHouse supports combining results of multiple SELECT statements using the UNION clause, with two main variants:

 

·      UNION ALL: includes all rows, even duplicates.

·      UNION DISTINCT: automatically removes duplicates from the final result.

 

If you don't specify ALL or DISTINCT, it will depend on the union_default_mode setting.

 

Syntax

SELECT ... 
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

   

The columns from all SELECTs must have the same number and compatible types. Use UNION ALL for better performance if duplicates don't matter.

 

1. DDL to create employees and supervisors tables

 

CREATE DATABASE IF NOT EXISTS demo_db;
DROP TABLE IF EXISTS demo_db.employees;
DROP TABLE IF EXISTS demo_db.supervisors;

CREATE TABLE demo_db.employees (
    id UInt32,
    name String
) ENGINE = Memory;

CREATE TABLE demo_db.supervisors (
    id UInt32,
    name String
) ENGINE = Memory;

2. Insert Sample Data

-- Employees Table
INSERT INTO demo_db.employees VALUES (1, 'Ram'), (2, 'Krishna'), (3, 'Chamu');

-- Supervisors Table
INSERT INTO demo_db.supervisors VALUES (2, 'Krishna'), (4, 'Sailu');

krishna :) SELECT * FROM demo_db.employees;

SELECT *
FROM demo_db.employees

Query id: e5f69997-5c4c-4363-b47c-2983de9f8d7b

   ┌─id─┬─name────┐
1. │  1 │ Ram     │
2. │  2 │ Krishna │
3. │  3 │ Chamu   │
   └────┴─────────┘

3 rows in set. Elapsed: 0.002 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.supervisors;

SELECT *
FROM demo_db.supervisors

Query id: 43acbc61-9ac9-4e02-9423-e5a607eb2db6

   ┌─id─┬─name────┐
1. │  2 │ Krishna │
2. │  4 │ Sailu   │
   └────┴─────────┘

2 rows in set. Elapsed: 0.001 sec.

In summary:

·      Krishna appears in both tables (overlap).

·      Sailu is only a supervisor.

·      Ram and Chamu are only employees.

 

3. UNION ALL (Keep Duplicates)

 

SELECT id, name FROM demo_db.employees
UNION ALL
SELECT id, name FROM demo_db.supervisors;

krishna :) SELECT id, name FROM demo_db.employees
UNION ALL
SELECT id, name FROM demo_db.supervisors;

SELECT
    id,
    name
FROM demo_db.employees
UNION ALL
SELECT
    id,
    name
FROM demo_db.supervisors

Query id: 63ae6297-c7b8-40c8-8bb0-a7aa77cff117

   ┌─id─┬─name────┐
1. │  1 │ Ram     │
2. │  2 │ Krishna │
3. │  3 │ Chamu   │
4. │  2 │ Krishna │
5. │  4 │ Sailu   │
   └────┴─────────┘

5 rows in set. Elapsed: 0.002 sec.

   

As you observe the output, you can see the record (2, Krishna) is repeated twice.

 

4. UNION DISTINCT (Remove Duplicates)

 

SELECT id, name FROM demo_db.employees
UNION DISTINCT
SELECT id, name FROM demo_db.supervisors;

krishna :) SELECT id, name FROM demo_db.employees
UNION DISTINCT
SELECT id, name FROM demo_db.supervisors;

SELECT
    id,
    name
FROM demo_db.employees
UNION DISTINCT
SELECT
    id,
    name
FROM demo_db.supervisors

Query id: f04a396c-ba60-4557-896f-467ed490f2f7

   ┌─id─┬─name────┐
1. │  1 │ Ram     │
2. │  2 │ Krishna │
3. │  3 │ Chamu   │
4. │  4 │ Sailu   │
   └────┴─────────┘

4 rows in set. Elapsed: 0.002 sec.

   

You can observe from above output, the duplicate record (2, Krishna) is merged into one row.

 

5. Using union_default_mode property.

If you don't specify ALL or DISTINCT in UNION clause, it will depend on the union_default_mode setting.

 

-- Optional: Set union behavior explicitly
SET union_default_mode = 'DISTINCT';

-- Will behave like UNION DISTINCT
SELECT id, name FROM demo_db.employees
UNION
SELECT id, name FROM demo_db.supervisors;

krishna :) SET union_default_mode = 'DISTINCT';

SET union_default_mode = 'DISTINCT'

Query id: 136d2888-aa4b-4f0e-b993-77566badf339

Ok.

0 rows in set. Elapsed: 0.003 sec. 

krishna :) ;

Empty query

krishna :) SELECT id, name FROM demo_db.employees
UNION
SELECT id, name FROM demo_db.supervisors;

SELECT
    id,
    name
FROM demo_db.employees
UNION
SELECT
    id,
    name
FROM demo_db.supervisors

Query id: 64841961-031f-4c40-b513-18e4e9ee79fc

   ┌─id─┬─name────┐
1. │  2 │ Krishna │
2. │  4 │ Sailu   │
3. │  1 │ Ram     │
4. │  3 │ Chamu   │
   └────┴─────────┘

4 rows in set. Elapsed: 0.002 sec.

   

In summary,

·      UNION is a powerful SQL construct supported in ClickHouse to combine the results of multiple SELECT queries into a single result set.

·      If you don’t explicitly specify ALL or DISTINCT, ClickHouse will default to the mode defined by the union_default_mode setting (usually DISTINCT).

 

  

Previous                                                    Next                                                    Home