Joins in ClickHouse: Inner, Left, Right, Full, and Cross Explained

  

ClickHouse is a powerful columnar database widely used for real-time analytics. While it's very fast for aggregations and filtering, many developers—especially those transitioning from traditional relational databases often find its SQL joins slightly different or confusing.

 

This post helps you to understand Joins in ClickHouse, and I am going to cover following topics.

·      Inner Join

·      Left Join

·      Right Join

·      Full Outer Join (simulated)

·      Cross Join

 

I'll walk through each join type using practical, easy-to-understand examples involving two simple tables employee and address. This helps not only in understanding the syntax and behavior of each join, but also how to apply them in real-world data analytics scenarios.

 

Whether you're just getting started with ClickHouse or looking to deepen your understanding of SQL join operations in a columnar engine, this post will give you a solid foundation.

 

1. Preparing Dataset to demo the Join examples

Let’s dive deep into the different types of joins in ClickHouse, using practical examples. To demo the examples, I am going to use two tables:

 

·      employee: contains employee information.

·      address: contains employee addresses.

 

1.1 Table Definitions

employee table

emp_id	name	department
1	Rahul Sharma	HR
2	Priya Mehra	IT
3	Arjun Verma	Finance
4	Neha Kapoor	Marketing
5	Aman Gupta	Sales

 

address table

emp_id	city	state
1	Mumbai	Maharashtra
2	Bengaluru	Karnataka
3	Delhi	Delhi
6	Hyderabad	Telangana

 

1.2 DDL to create tables

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.employee
(
    emp_id UInt32,
    name String,
    department String
) ENGINE = MergeTree()
ORDER BY emp_id;

CREATE TABLE demo_db.address
(
    emp_id UInt32,
    city String,
    state String
) ENGINE = MergeTree()
ORDER BY emp_id;

   

1.3 Insert statements for sample Data

 

employee Table:

 

INSERT INTO demo_db.employee (emp_id, name, department) VALUES
(1, 'Rahul Sharma', 'HR'),
(2, 'Priya Mehra', 'IT'),
(3, 'Arjun Verma', 'Finance'),
(4, 'Neha Kapoor', 'Marketing'),
(5, 'Aman Gupta', 'Sales');

   

address Table:

 

INSERT INTO demo_db.address (emp_id, city, state) VALUES
(1, 'Mumbai', 'Maharashtra'),
(2, 'Bengaluru', 'Karnataka'),
(3, 'Delhi', 'Delhi'),
(6, 'Hyderabad', 'Telangana');

   

1.4 Print the records of employee and address tables.

 

krishna :) SELECT * FROM demo_db.employee;

SELECT *
FROM demo_db.employee

Query id: d57139f1-b9d0-447e-b1e3-36cb8e34814b

   ┌─emp_id─┬─name─────────┬─department─┐
1. │      1 │ Rahul Sharma │ HR         │
2. │      2 │ Priya Mehra  │ IT         │
3. │      3 │ Arjun Verma  │ Finance    │
4. │      4 │ Neha Kapoor  │ Marketing  │
5. │      5 │ Aman Gupta   │ Sales      │
   └────────┴──────────────┴────────────┘

5 rows in set. Elapsed: 0.013 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.address;

SELECT *
FROM demo_db.address

Query id: ef83be21-d120-4529-b1c2-fd6e4e842e2c

   ┌─emp_id─┬─city──────┬─state───────┐
1. │      1 │ Mumbai    │ Maharashtra │
2. │      2 │ Bengaluru │ Karnataka   │
3. │      3 │ Delhi     │ Delhi       │
4. │      6 │ Hyderabad │ Telangana   │
   └────────┴───────────┴─────────────┘

4 rows in set. Elapsed: 0.005 sec.

   

2. INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables based on the condition specified in the ON clause.

 

Key Characteristics

·      Only matching rows from both tables are returned.

·      If there is no match, the row is excluded from the result.

·      It is the default join type (i.e., JOIN alone means INNER JOIN).

 

Example

 

SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
INNER JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

   

(or) you can omit INNER

SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;  

Above SQL queries return only the records that have matching emp_id in both tables.

krishna :) SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

SELECT
    e.emp_id,
    e.name,
    e.department,
    a.city,
    a.state
FROM demo_db.employee AS e
INNER JOIN demo_db.address AS a ON e.emp_id = a.emp_id

Query id: cbbc8fcd-5e0f-475c-a1ff-89fc959f8ec0

   ┌─emp_id─┬─name─────────┬─department─┬─city──────┬─state───────┐
1. │      1 │ Rahul Sharma │ HR         │ Mumbai    │ Maharashtra │
2. │      2 │ Priya Mehra  │ IT         │ Bengaluru │ Karnataka   │
3. │      3 │ Arjun Verma  │ Finance    │ Delhi     │ Delhi       │
   └────────┴──────────────┴────────────┴───────────┴─────────────┘

3 rows in set. Elapsed: 0.007 sec. 

krishna :) ;

Empty query

krishna :) SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
INNER JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

SELECT
    e.emp_id,
    e.name,
    e.department,
    a.city,
    a.state
FROM demo_db.employee AS e
INNER JOIN demo_db.address AS a ON e.emp_id = a.emp_id

Query id: 6cdbda4f-ed22-4dd8-baab-a3ae735e45d6

   ┌─emp_id─┬─name─────────┬─department─┬─city──────┬─state───────┐
1. │      1 │ Rahul Sharma │ HR         │ Mumbai    │ Maharashtra │
2. │      2 │ Priya Mehra  │ IT         │ Bengaluru │ Karnataka   │
3. │      3 │ Arjun Verma  │ Finance    │ Delhi     │ Delhi       │
   └────────┴──────────────┴────────────┴───────────┴─────────────┘

3 rows in set. Elapsed: 0.007 sec.

   

3. LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). If there is no match, the result is NULL for columns from the right table.

 

Explanation in detail

·      LEFT JOIN (also called LEFT OUTER JOIN) includes all rows from the left table.

·      For each row in the left table:

o   If a matching row is found in the right table (based on the ON condition), the columns from the right table are included.

o   If no match is found, NULL values are used for the columns from the right table.

·      The number of rows returned is at least as many as in the left table.

 

 

SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
LEFT JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

   

Above statement returns all records from employee, and the matched records from address. If no match, NULL values are shown for address data.

 

krishna :) SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
LEFT JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

SELECT
    e.emp_id,
    e.name,
    e.department,
    a.city,
    a.state
FROM demo_db.employee AS e
LEFT JOIN demo_db.address AS a ON e.emp_id = a.emp_id

Query id: 87231877-dcc8-4c5f-9400-c11ca9cb24fa

   ┌─emp_id─┬─name─────────┬─department─┬─city──────┬─state───────┐
1. │      1 │ Rahul Sharma │ HR         │ Mumbai    │ Maharashtra │
2. │      2 │ Priya Mehra  │ IT         │ Bengaluru │ Karnataka   │
3. │      3 │ Arjun Verma  │ Finance    │ Delhi     │ Delhi       │
4. │      4 │ Neha Kapoor  │ Marketing  │           │             │
5. │      5 │ Aman Gupta   │ Sales      │           │             │
   └────────┴──────────────┴────────────┴───────────┴─────────────┘

5 rows in set. Elapsed: 0.003 sec.

   

As you see the result, All employees are listed from employees table, even if address is missing for the records 4 and 5.

 

4. RIGHT JOIN

A RIGHT JOIN returns all rows from the right table, and the matching rows from the left table.

If there is no match, the result will have NULLs for columns from the left table.

 

SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
RIGHT JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

krishna :) SELECT 
    e.emp_id, e.name, e.department,
    a.city, a.state
FROM demo_db.employee AS e
RIGHT JOIN demo_db.address AS a 
ON e.emp_id = a.emp_id;

SELECT
    e.emp_id,
    e.name,
    e.department,
    a.city,
    a.state
FROM demo_db.employee AS e
RIGHT JOIN demo_db.address AS a ON e.emp_id = a.emp_id

Query id: 86435bf2-2f1a-4112-bf14-a2647e3a158d

   ┌─emp_id─┬─name─────────┬─department─┬─city──────┬─state───────┐
1. │      1 │ Rahul Sharma │ HR         │ Mumbai    │ Maharashtra │
2. │      2 │ Priya Mehra  │ IT         │ Bengaluru │ Karnataka   │
3. │      3 │ Arjun Verma  │ Finance    │ Delhi     │ Delhi       │
4. │      0 │              │            │ Hyderabad │ Telangana   │
   └────────┴──────────────┴────────────┴───────────┴─────────────┘

4 rows in set. Elapsed: 0.008 sec. 

As you observe the result of RIGHT JOIN query, it returns all records from address, and the matched records from employee, whereover there is no match in employee table, it has NULL values for the columns.

 

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning it joins every row of the first table with every row of the second table, regardless of any condition. If table A has m rows and table B has n rows, the CROSS JOIN will return m × n rows.

 

Example

SELECT 
    e.emp_id AS emp_id_e, e.name, e.department,
    a.emp_id AS emp_id_a, a.city, a.state
FROM demo_db.employee AS e
CROSS JOIN demo_db.address AS a;

krishna :) SELECT 
    e.emp_id AS emp_id_e, e.name, e.department,
    a.emp_id AS emp_id_a, a.city, a.state
FROM demo_db.employee AS e
CROSS JOIN demo_db.address AS a;

SELECT
    e.emp_id AS emp_id_e,
    e.name,
    e.department,
    a.emp_id AS emp_id_a,
    a.city,
    a.state
FROM demo_db.employee AS e
CROSS JOIN demo_db.address AS a

Query id: 9de64cd6-3364-4e7d-838d-2bd524b1dc91

    ┌─emp_id_e─┬─name─────────┬─department─┬─emp_id_a─┬─city──────┬─state───────┐
 1. │        1 │ Rahul Sharma │ HR         │        1 │ Mumbai    │ Maharashtra │
 2. │        1 │ Rahul Sharma │ HR         │        2 │ Bengaluru │ Karnataka   │
 3. │        1 │ Rahul Sharma │ HR         │        3 │ Delhi     │ Delhi       │
 4. │        1 │ Rahul Sharma │ HR         │        6 │ Hyderabad │ Telangana   │
 5. │        2 │ Priya Mehra  │ IT         │        1 │ Mumbai    │ Maharashtra │
 6. │        2 │ Priya Mehra  │ IT         │        2 │ Bengaluru │ Karnataka   │
 7. │        2 │ Priya Mehra  │ IT         │        3 │ Delhi     │ Delhi       │
 8. │        2 │ Priya Mehra  │ IT         │        6 │ Hyderabad │ Telangana   │
 9. │        3 │ Arjun Verma  │ Finance    │        1 │ Mumbai    │ Maharashtra │
10. │        3 │ Arjun Verma  │ Finance    │        2 │ Bengaluru │ Karnataka   │
11. │        3 │ Arjun Verma  │ Finance    │        3 │ Delhi     │ Delhi       │
12. │        3 │ Arjun Verma  │ Finance    │        6 │ Hyderabad │ Telangana   │
13. │        4 │ Neha Kapoor  │ Marketing  │        1 │ Mumbai    │ Maharashtra │
14. │        4 │ Neha Kapoor  │ Marketing  │        2 │ Bengaluru │ Karnataka   │
15. │        4 │ Neha Kapoor  │ Marketing  │        3 │ Delhi     │ Delhi       │
16. │        4 │ Neha Kapoor  │ Marketing  │        6 │ Hyderabad │ Telangana   │
17. │        5 │ Aman Gupta   │ Sales      │        1 │ Mumbai    │ Maharashtra │
18. │        5 │ Aman Gupta   │ Sales      │        2 │ Bengaluru │ Karnataka   │
19. │        5 │ Aman Gupta   │ Sales      │        3 │ Delhi     │ Delhi       │
20. │        5 │ Aman Gupta   │ Sales      │        6 │ Hyderabad │ Telangana   │
    └──────────┴──────────────┴────────────┴──────────┴───────────┴─────────────┘

20 rows in set. Elapsed: 0.008 sec.

   

6. FULL JOIN

A FULL JOIN (also called FULL OUTER JOIN) returns all records from both tables, matching where possible, and filling in NULLs where there's no match on either side.

 

In summary, a FULL JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN. It returns:

·      Matching rows from both tables

·      Unmatched rows from the left table (with NULLs for right)

·      Unmatched rows from the right table (with NULLs for left)

 

Example

 

SELECT 
    e.emp_id AS emp_id_e, e.name, e.department,
    a.emp_id AS emp_id_a, a.city, a.state
FROM demo_db.employee AS e
FULL JOIN demo_db.address AS a
ON e.emp_id=a.emp_id;

krishna :) SELECT 
    e.emp_id AS emp_id_e, e.name, e.department,
    a.emp_id AS emp_id_a, a.city, a.state
FROM demo_db.employee AS e
FULL JOIN demo_db.address AS a
ON e.emp_id=a.emp_id;

SELECT
    e.emp_id AS emp_id_e,
    e.name,
    e.department,
    a.emp_id AS emp_id_a,
    a.city,
    a.state
FROM demo_db.employee AS e
FULL OUTER JOIN demo_db.address AS a ON e.emp_id = a.emp_id

Query id: 087fecca-4e1e-487d-a694-a6e7efa99fe4

   ┌─emp_id_e─┬─name─────────┬─department─┬─emp_id_a─┬─city──────┬─state───────┐
1. │        1 │ Rahul Sharma │ HR         │        1 │ Mumbai    │ Maharashtra │
2. │        2 │ Priya Mehra  │ IT         │        2 │ Bengaluru │ Karnataka   │
3. │        3 │ Arjun Verma  │ Finance    │        3 │ Delhi     │ Delhi       │
4. │        4 │ Neha Kapoor  │ Marketing  │        0 │           │             │
5. │        5 │ Aman Gupta   │ Sales      │        0 │           │             │
6. │        0 │              │            │        6 │ Hyderabad │ Telangana   │
   └──────────┴──────────────┴────────────┴──────────┴───────────┴─────────────┘

6 rows in set. Elapsed: 0.007 sec.

   

In summary, we explored the five fundamental types of SQL joins—INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN—in the context of ClickHouse. Using practical examples, I demonstrated how each join type behaves with a real time example.

  

Previous                                                    Next                                                    Home