Introduction

In multi-table join queries, JOIN is the most common operation and often the primary performance bottleneck. As a columnar analytical database, DuckDB uses Hash Join by default for most join scenarios, but switches to Merge Join under specific conditions. Understanding the differences between these two JOIN algorithms, and how to optimize multi-table joins through materialization strategies, is key to improving query performance.

This article demonstrates how to use EXPLAIN ANALYZE to diagnose JOIN performance issues and provides actionable optimization recommendations through real-world business scenarios.

Setting Up Test Data

CREATE TABLE orders AS
SELECT * FROM (VALUES
  (1, 101, DATE '2026-01-15', 299.99),
  (2, 102, DATE '2026-01-16', 89.50),
  (3, 103, DATE '2026-01-17', 450.00),
  (4, 101, DATE '2026-01-18', 120.00),
  (5, 104, DATE '2026-01-19', 67.80),
  (6, 105, DATE '2026-01-20', 310.00),
  (7, 102, DATE '2026-01-21', 225.50),
  (8, 106, DATE '2026-01-22', 99.99),
  (9, 103, DATE '2026-01-23', 540.00),
  (10, 107, DATE '2026-01-24', 180.00)
) AS t(order_id, customer_id, order_date, amount);

CREATE TABLE customers AS
SELECT * FROM (VALUES
  (101, 'Alice', 'electronics'),
  (102, 'Bob', 'clothing'),
  (103, 'Charlie', 'electronics'),
  (104, 'Diana', 'food'),
  (105, 'Eve', 'clothing'),
  (106, 'Frank', 'books'),
  (107, 'Grace', 'electronics')
) AS t(customer_id, name, category);

CREATE TABLE categories AS
SELECT * FROM (VALUES
  ('electronics', 0.10),
  ('clothing', 0.05),
  ('food', 0.02),
  ('books', 0.03)
) AS t(category, tax_rate);

1. Hash Join: DuckDB’s Default Choice

Hash Join works in two phases: Build and Probe.

1. Build Phase: The smaller table (driving table) is loaded into memory, hash values are computed on the JOIN key, and a hash table is constructed.
2. Probe Phase: Rows from the larger table (probed table) are scanned one by one, looking up matching records in the hash table based on the JOIN key.

EXPLAIN ANALYZE
SELECT o.order_id, c.name, c.category, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Figure: Two-phase flow of Hash Join — build and probe

Expected output:

┌─────────────────────────────────────────────────────────────┐
│ Hash Join → Hash: (o.customer_id = c.customer_id)         │
│   -> Outer Scan: orders (10 rows)                         │
│   -> Inner Build: customers (7 rows)                      │
│ Execution Time: ~0.15ms                                   │
└─────────────────────────────────────────────────────────────┘

Key observations:

• DuckDB automatically selects the smaller table (customers, 7 rows) as the build side.
• For medium and small datasets, Hash Join is typically the fastest choice with near O(n) time complexity.

Hash Join Performance at Scale

When data grows, Hash Join’s advantages become even more pronounced. Here’s an e-commerce scenario with larger datasets:

-- Generate larger test data
CREATE TABLE large_orders AS
SELECT
  i AS order_id,
  (random() * 1000 + 1)::INTEGER AS customer_id,
  DATE '2026-01-01' + (i % 365) AS order_date,
  ROUND((random() * 500 + 10)::NUMERIC, 2) AS amount
FROM generate_series(1, 100000) AS s(i);

CREATE TABLE large_customers AS
SELECT
  i AS customer_id,
  'User_' || i AS name,
  CASE i % 4
    WHEN 0 THEN 'electronics'
    WHEN 1 THEN 'clothing'
    WHEN 2 THEN 'food'
    ELSE 'books'
  END AS category
FROM generate_series(1, 10000) AS s(i);

EXPLAIN ANALYZE
SELECT
  c.category,
  COUNT(o.order_id) AS order_count,
  SUM(o.amount) AS total_amount
FROM large_orders o
JOIN large_customers c ON o.customer_id = c.customer_id
GROUP BY c.category
ORDER BY total_amount DESC;

Terminal output:

┌──────────────────────────────────────────────────────────────┐
│ Hash Join                                                    │
│   Hash Cond: (o.customer_id = c.customer_id)                │
│   -> Seq Scan on large_orders (100,000 rows)                │
│   -> Hash (build)                                            │
│       -> Seq Scan on large_customers (10,000 rows)           │
│                                                              │
│ Aggregate: GROUP BY c.category                               │
│ Execution Time: 12.34 ms                                    │
└──────────────────────────────────────────────────────────────┘

2. Merge Join: The Perfect Partner for Sorted Data

Merge Join requires both tables to be sorted by the JOIN key. Its process is similar to the merge step in merge sort:

1. Both tables are sorted by the JOIN key.
2. Two pointers scan simultaneously, matching rows with equal keys.

SET enable_merge_join = true;

EXPLAIN ANALYZE
SELECT o.order_id, c.name, o.amount
FROM large_orders o
JOIN large_customers c ON o.customer_id = c.customer_id
WHERE o.customer_id BETWEEN 1 AND 500;

Figure: Two-pointer ordered merge process of Merge Join

When data is already sorted, or when the optimizer determines that sorting is cheaper than building a hash table, DuckDB will prefer Merge Join. In columnar storage, if the JOIN key happens to be a sorted column, Merge Join can avoid additional sorting overhead.

Hash Join vs Merge Join Comparison

3. Materialization Strategies for Multi-Table JOINs

When joining multiple tables, how intermediate results are handled significantly impacts performance. DuckDB offers two main materialization approaches:

3.1 Lazy Evaluation

DuckDB defaults to lazy evaluation, only computing intermediate expressions when the final result is needed. For multi-table JOINs, this means the query planner can reorder joins for optimal execution.

EXPLAIN ANALYZE
SELECT
  c.category,
  COUNT(*) AS order_count,
  AVG(o.amount) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN categories cat ON c.category = cat.category
GROUP BY c.category;

3.2 Explicit Materialization

In certain scenarios, you can use CTEs or temporary tables to force materialization of intermediate results, avoiding redundant computation.

-- Method 1: Materialize via CTE
WITH customer_orders AS (
  SELECT
    c.customer_id,
    c.name,
    c.category,
    o.order_id,
    o.amount
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
)
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue
FROM customer_orders
GROUP BY category
ORDER BY total_revenue DESC;

-- Method 2: Create a materialized temp table
CREATE TEMP TABLE joined_data AS
SELECT
  o.order_id,
  c.customer_id,
  c.name,
  c.category,
  o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

SELECT
  category,
  COUNT(*) AS order_count,
  ROUND(SUM(amount), 2) AS total_revenue
FROM joined_data
GROUP BY category
ORDER BY total_revenue DESC;

Terminal output (after materialization):

┌──────────────────────────────────────────────────────┐
│ category    | order_count | total_revenue          │
│-------------|-------------|------------------------│
│ electronics |     4       |     1369.99            │
│ clothing    |     3       |      625.00            │
│ food        |     1       |       67.80            │
│ books       |     1       |       99.99            │
└──────────────────────────────────────────────────────┘

3.3 When to Materialize?

• Intermediate results are referenced multiple times: CTEs or subqueries used in several places.
• Filtering dramatically reduces row count: JOINing before filtering may produce大量 useless intermediate data.
• Complex aggregation chains: Materialization improves readability and execution efficiency in multi-level nested aggregations.

4. EXPLAIN ANALYZE in Practice

Using EXPLAIN ANALYZE to inspect actual execution plans is the first step in diagnosing JOIN issues.

EXPLAIN ANALYZE
SELECT
  o.order_id,
  c.name,
  cat.tax_rate,
  o.amount * (1 + cat.tax_rate) AS final_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN categories cat ON c.category = cat.category;

Figure: Multi-table JOIN execution plan shown by EXPLAIN ANALYZE

Tuning tips:

1. Check Join Order: The optimizer usually picks the best table join order, but manual adjustment may be needed sometimes.
2. Add Indexes: While DuckDB is columnar, sorting or indexing high-frequency JOIN keys can improve performance.
3. Reduce Joined Columns: Select only needed columns to reduce memory and I/O overhead.
4. Use FILTER Instead of WHERE: In aggregation scenarios, FILTER can complete multi-condition statistics in one pass.

5. Real-World Scenario: E-Commerce Sales Analysis

As an e-commerce data analyst, you need to analyze sales performance by category and calculate final prices with tax.

WITH daily_sales AS (
  SELECT
    DATE_TRUNC('day', o.order_date) AS sale_date,
    c.category,
    COUNT(*) AS order_count,
    SUM(o.amount) AS gross_revenue,
    AVG(o.amount) AS avg_order_value
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY DATE_TRUNC('day', o.order_date), c.category
)
SELECT
  sale_date,
  category,
  order_count,
  gross_revenue,
  avg_order_value,
  LAG(gross_revenue) OVER (
    PARTITION BY category ORDER BY sale_date
  ) AS prev_day_revenue,
  ROUND(
    (gross_revenue - LAG(gross_revenue) OVER (
      PARTITION BY category ORDER BY sale_date
    )) / NULLIF(LAG(gross_revenue) OVER (
      PARTITION BY category ORDER BY sale_date
    ), 0) * 100, 2
  ) AS revenue_change_pct
FROM daily_sales
ORDER BY sale_date, category;

This query combines multi-table JOINs, window functions, and date aggregation — a typical e-commerce analytics scenario. By organizing JOIN order properly and using CTEs, queries can be both efficient and readable.

Summary

DuckDB’s query optimizer is already very intelligent and will automatically select the optimal JOIN strategy in most cases. But in complex queries, understanding the underlying principles and actively tuning can still yield significant performance improvements.

For more DuckDB practical tips, follow DuckDB Lab (duckdblab.org).