Introduction
Joins are among the most performance-sensitive operations in any analytical database. Whether you're combining fact and dimension tables, filtering records based on related datasets, or performing data quality checks, the efficiency of your JOIN operations directly impacts query execution time and memory consumption.
ClickHouse® has continuously improved its JOIN execution engine over the years, making complex analytical queries faster and more resource-efficient. One of the notable enhancements in ClickHouse® 26.3 is the expansion of automatic JOIN reordering to additional JOIN types, including SEMI, ANTI, and FULL OUTER JOIN.
Prior to version 26.3, the query optimizer could automatically reorder only INNER and LEFT/RIGHT JOINs. With this release, the optimizer can now evaluate table statistics and automatically choose a more efficient join order for a wider range of JOIN types, reducing memory usage and improving query performance without requiring manual query rewrites.
In this article, we'll review the major JOIN types available in ClickHouse®, understand how automatic JOIN reordering works, explore what's new in ClickHouse® 26.3, and discuss practical best practices for writing efficient JOIN queries.
Understanding JOIN Types
Before exploring the optimizer improvements, it's important to understand what each JOIN type actually returns.
| JOIN Type | Returns |
|---|---|
| INNER JOIN | Only matching rows from both tables |
| LEFT OUTER JOIN | All rows from the left table plus matching rows from the right |
| RIGHT OUTER JOIN | All rows from the right table plus matching rows from the left |
| FULL OUTER JOIN | All rows from both tables |
| LEFT SEMI JOIN | Left rows that have a matching row (without returning right-side columns) |
| LEFT ANTI JOIN | Left rows that have no matching row |
Each JOIN serves a different purpose, and selecting the appropriate one can improve both correctness and performance.
Sample Tables
Throughout this article, we'll use two simple tables.
CREATE TABLE default.customers
(
customer_id UInt32,
name String
)
ENGINE = MergeTree
ORDER BY customer_id;
INSERT INTO default.customers VALUES
(1,'Alice'),
(2,'Bob'),
(3,'Charlie');
CREATE TABLE default.orders
(
order_id UInt32,
customer_id UInt32,
amount Float64
)
ENGINE = MergeTree
ORDER BY order_id;
INSERT INTO default.orders VALUES
(101,1,1200),
(102,2,450);
Notice that Charlie has not placed any orders, making it easy to see how different JOIN types behave.
INNER JOIN
An INNER JOIN returns only rows that exist in both tables.
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 1200 |
| 2 | Bob | 102 | 450 |
Charlie is excluded because no matching order exists.
Use when: You only need records that exist in both tables.
LEFT OUTER JOIN
A LEFT JOIN returns every row from the left table, regardless of whether a matching row exists on the right.
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 1200 |
| 2 | Bob | 102 | 450 |
| 3 | Charlie | NULL | NULL |
Charlie appears because every customer is preserved.
Use when: You need all rows from the left table.
RIGHT OUTER JOIN
A RIGHT JOIN returns every row from the right table.
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;
Since every order belongs to a customer, the result contains only Alice and Bob.
Use when: You need all rows from the right table.
FULL OUTER JOIN
A FULL OUTER JOIN returns every row from both tables.
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
Result:
| customer_id | name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 1200 |
| 2 | Bob | 102 | 450 |
| 3 | Charlie | NULL | NULL |
Rows without matches are filled with NULL values.
Use when: You need a complete combined view of both datasets.
LEFT SEMI JOIN
A SEMI JOIN checks whether a match exists but returns only columns from the left table.
SELECT
c.customer_id,
c.name
FROM customers c
LEFT SEMI JOIN orders o
ON c.customer_id = o.customer_id;
Result:
| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Notice that no columns from the orders table are returned.
This is generally more efficient than using an INNER JOIN when you only need to verify that a related row exists.
Use when: Checking existence without retrieving data from the right table.
LEFT ANTI JOIN
ANTI JOIN is the opposite of SEMI JOIN.
It returns rows that do not have a matching record.
SELECT
c.customer_id,
c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;
Result:
| customer_id | name |
|---|---|
| 3 | Charlie |
Only Charlie is returned because he has never placed an order.
Use when:
- Finding orphaned records
- Data validation
- Missing relationships
- Customers who have never purchased
JOIN Comparison
| JOIN Type | Alice | Bob | Charlie |
|---|---|---|---|
| INNER JOIN | ✅ | ✅ | ❌ |
| LEFT JOIN | ✅ | ✅ | ✅ (NULL) |
| RIGHT JOIN | ✅ | ✅ | ❌ |
| FULL OUTER JOIN | ✅ | ✅ | ✅ (NULL) |
| LEFT SEMI JOIN | ✅ | ✅ | ❌ |
| LEFT ANTI JOIN | ❌ | ❌ | ✅ |
What's New in ClickHouse® 26.3?
ClickHouse executes hash joins by building an in-memory hash table from one side of the JOIN.
If the larger table becomes the hash table, memory consumption increases significantly.
Before ClickHouse® 26.3, the optimizer could automatically swap JOIN order only for:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
For SEMI, ANTI, and FULL OUTER JOIN, developers often needed to manually arrange tables in the most efficient order.
ClickHouse® 26.3 removes much of this manual work.
The optimizer now evaluates table statistics and can automatically reorder:
- SEMI JOIN
- ANTI JOIN
- FULL OUTER JOIN
to build smaller hash tables whenever possible.
For example:
SELECT
c.customer_id,
c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;
Even if the query isn't written in the optimal order, ClickHouse® 26.3 can internally rearrange the join plan to improve efficiency.
Collecting Statistics for Better Optimization
Automatic JOIN reordering relies on accurate table statistics.
It is recommended to collect statistics on:
- JOIN key columns
- Frequently filtered columns
Useful statistics include:
TDigest
Provides data distribution and quantile estimates.
Useful for estimating filter selectivity.
ALTER TABLE orders
ADD STATISTICS customer_id TYPE tdigest;
ALTER TABLE orders
MATERIALIZE STATISTICS customer_id;
Uniq
Estimates column cardinality.
Useful for predicting JOIN selectivity.
CountMinSketch
Useful when filtering frequently on exact values.
Provides approximate frequency estimates with minimal memory.
Performance Best Practices
1. Prefer SEMI JOIN over INNER JOIN
Instead of:
SELECT customer_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
Use:
SELECT customer_id
FROM customers
LEFT SEMI JOIN orders
ON customers.customer_id = orders.customer_id;
This avoids reading unnecessary columns.
2. Prefer ANTI JOIN over NOT IN
Instead of:
SELECT customer_id
FROM customers
WHERE customer_id NOT IN
(
SELECT customer_id
FROM orders
);
Use:
SELECT customer_id
FROM customers
LEFT ANTI JOIN orders
USING(customer_id);
ANTI JOIN is typically faster and more memory-efficient on large datasets.
3. Let the Optimizer Help
Older ClickHouse versions often required manually placing the smaller table on the right.
With ClickHouse® 26.3, automatic JOIN reordering reduces the need for manual optimization across many JOIN types.
4. Verify Query Plans
Always inspect execution plans.
EXPLAIN
SELECT
c.customer_id,
c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;
EXPLAIN helps verify that the optimizer is selecting the expected execution strategy.
When Will This Improvement Matter?
You'll benefit the most if your workloads include:
- Large analytical datasets
- Multi-table joins
- Frequent SEMI or ANTI JOIN queries
- FULL OUTER JOIN operations
- Memory-sensitive workloads
- Data warehouse environments with complex reporting
For smaller datasets, the improvement may not be immediately noticeable, but at scale it helps reduce memory usage and improves overall query execution.
Conclusion
Choosing the correct JOIN type is one of the simplest ways to improve query performance in ClickHouse®.
While INNER, LEFT, and FULL OUTER JOIN cover most common scenarios, SEMI JOIN and ANTI JOIN are powerful alternatives that are often overlooked. They can reduce unnecessary data processing and improve memory efficiency when you only need to check whether matching rows exist.
ClickHouse® 26.3 builds on these capabilities by extending automatic JOIN reordering to SEMI, ANTI, and FULL OUTER JOINs. By leveraging table statistics, the optimizer can automatically choose a more efficient execution plan, reducing memory consumption and eliminating much of the manual tuning previously required.
It's another step toward making ClickHouse not only one of the fastest analytical databases available, but also one that increasingly optimizes itself behind the scenes—allowing developers to focus more on writing queries and less on fine-tuning execution strategies.
Top comments (0)