Why ASOF JOIN?

In data analysis, you frequently encounter this scenario: you have two time-series tables and need to match each row from the left table to the most recent row in the right table that occurred at or before the left row’s timestamp.

Common use cases include:

• Stock markets: Match each trade to the most recent quote to calculate the bid-ask spread at execution time
• IoT sensors: Align event logs with the latest sensor readings
• User behavior analytics: Match page clicks to the most recent session start
• Financial risk management: Associate each transaction with the latest account balance snapshot

In traditional SQL, this requires correlated subqueries with MAX() + GROUP BY, or window functions with self-joins — painful to write and notoriously slow to execute. DuckDB v1.5.0’s ASOF JOIN solves this elegantly.

What Is ASOF JOIN?

ASOF JOIN is a non-equi join type purpose-built for time-series data. Its core semantic: for each row in the left table, find the row in the right table that satisfies the match conditions and has the closest timestamp (not exceeding the left table’s timestamp).

DuckDB v1.5.0 “Variegata” officially introduced ASOF JOIN into core SQL syntax — before this, it was only available experimentally.

Basic Syntax

SELECT *
FROM left_table l
ASOF JOIN right_table r
  ON l.symbol = r.symbol        -- equality condition (optional but recommended)
  AND l.timestamp >= r.timestamp -- ASOF time condition
;

Key points:

1. Replace LEFT JOIN / INNER JOIN with ASOF JOIN
2. The ON clause requires at least one non-equi time condition (>=, >, <=, <)
3. Equality conditions (e.g., stock symbol, sensor ID) can be included alongside
4. Returns the single closest matching row from the right table

Hands-On Example 1: Stock Trades & Quotes

Let’s walk through a realistic stock market example.

Prepare Data

-- Create trades table
CREATE TABLE trades AS
SELECT * FROM (VALUES
  ('AAPL', TIMESTAMP '2026-05-01 09:30:05', 150.25),
  ('AAPL', TIMESTAMP '2026-05-01 09:30:12', 150.30),
  ('AAPL', TIMESTAMP '2026-05-01 09:30:18', 150.28),
  ('AAPL', TIMESTAMP '2026-05-01 09:31:00', 150.35),
  ('MSFT', TIMESTAMP '2026-05-01 09:30:10', 380.50),
  ('MSFT', TIMESTAMP '2026-05-01 09:30:22', 380.55),
  ('MSFT', TIMESTAMP '2026-05-01 09:31:05', 380.60)
) AS t(symbol, trade_time, trade_price);

-- Create quotes table
CREATE TABLE quotes AS
SELECT * FROM (VALUES
  ('AAPL', TIMESTAMP '2026-05-01 09:30:00', 150.20, 150.30),
  ('AAPL', TIMESTAMP '2026-05-01 09:30:10', 150.22, 150.32),
  ('AAPL', TIMESTAMP '2026-05-01 09:30:15', 150.25, 150.33),
  ('AAPL', TIMESTAMP '2026-05-01 09:31:00', 150.30, 150.40),
  ('MSFT', TIMESTAMP '2026-05-01 09:30:00', 380.40, 380.60),
  ('MSFT', TIMESTAMP '2026-05-01 09:30:20', 380.45, 380.62),
  ('MSFT', TIMESTAMP '2026-05-01 09:31:00', 380.50, 380.70)
) AS q(symbol, quote_time, bid, ask);

Matching with ASOF JOIN

SELECT
  t.symbol,
  t.trade_time,
  t.trade_price,
  q.quote_time,
  q.bid,
  q.ask,
  (q.ask - q.bid) AS spread,
  ROUND((t.trade_price - q.bid) / (q.ask - q.bid), 4) AS trade_position
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol
  AND t.trade_time >= q.quote_time
ORDER BY t.symbol, t.trade_time;

Results:

Each trade is accurately matched to the most recent quote that existed at or before the trade time — this is the core power of ASOF JOIN.

Comparison with Traditional Approaches

Before ASOF JOIN arrived in DuckDB, you had to resort to one of these:

Method 1: Subquery + MAX()

SELECT
  t.*,
  q.bid,
  q.ask
FROM trades t
LEFT JOIN quotes q
  ON t.symbol = q.symbol
  AND q.quote_time = (
    SELECT MAX(q2.quote_time)
    FROM quotes q2
    WHERE q2.symbol = t.symbol
      AND q2.quote_time <= t.trade_time
  );

Method 2: Window Function + Self-Join

WITH ranked AS (
  SELECT
    t.*,
    q.bid,
    q.ask,
    q.quote_time,
    ROW_NUMBER() OVER (
      PARTITION BY t.symbol, t.trade_time
      ORDER BY q.quote_time DESC
    ) AS rn
  FROM trades t, quotes q
  WHERE t.symbol = q.symbol
    AND q.quote_time <= t.trade_time
)
SELECT * FROM ranked WHERE rn = 1;

Performance Benchmark

Benchmark: DuckDB v1.5.0, M1 MacBook Pro 16GB. Two randomly generated time-series tables with left table having 3× the rows of the right table.

ASOF JOIN’s advantage grows dramatically with data volume — it uses a specialized algorithm (sort-merge + binary search) that avoids the Cartesian explosion of traditional methods.

Hands-On Example 2: IoT Sensor Alignment

In IoT scenarios, different sensors sample at different frequencies. ASOF JOIN aligns them to a unified timeline effortlessly.

-- Temperature sensor (every 5 seconds)
CREATE TABLE temp_sensor AS
SELECT * FROM (VALUES
  ('sensor_A', TIMESTAMP '2026-05-01 00:00:00', 22.5),
  ('sensor_A', TIMESTAMP '2026-05-01 00:00:05', 22.7),
  ('sensor_A', TIMESTAMP '2026-05-01 00:00:10', 22.6)
) AS t(device_id, ts, temperature);

-- Humidity sensor (every 10 seconds)
CREATE TABLE humidity_sensor AS
SELECT * FROM (VALUES
  ('sensor_A', TIMESTAMP '2026-05-01 00:00:02', 45.0),
  ('sensor_A', TIMESTAMP '2026-05-01 00:00:12', 45.3)
) AS h(device_id, ts, humidity);

-- ASOF JOIN alignment
SELECT
  t.ts,
  t.temperature,
  h.humidity
FROM temp_sensor t
ASOF JOIN humidity_sensor h
  ON t.device_id = h.device_id
  AND t.ts >= h.ts
ORDER BY t.ts;

The result aligns each temperature reading with the most recent humidity reading — no complex interpolation logic needed.

Hands-On Example 3: Log & Event Correlation

In observability pipelines, you often need to correlate application logs with infrastructure events (deployments, config changes):

-- Create a larger-scale demo
CREATE TABLE app_logs AS
SELECT
  range AS log_id,
  'service-' || (range % 5 + 1) AS service_name,
  TIMESTAMP '2026-05-01 00:00:00' + INTERVAL (range) SECOND AS log_time,
  CASE (range % 3)
    WHEN 0 THEN 'INFO'
    WHEN 1 THEN 'WARN'
    ELSE 'ERROR'
  END AS log_level,
  'log message #' || range AS message
FROM range(1, 100000);

CREATE TABLE deployments AS
SELECT * FROM (VALUES
  ('service-1', TIMESTAMP '2026-05-01 00:00:00', 'v2.1.0'),
  ('service-1', TIMESTAMP '2026-05-01 06:00:00', 'v2.1.1'),
  ('service-2', TIMESTAMP '2026-05-01 00:00:00', 'v3.0.0'),
  ('service-2', TIMESTAMP '2026-05-01 08:00:00', 'v3.0.1'),
  ('service-3', TIMESTAMP '2026-05-01 00:00:00', 'v1.5.0')
) AS d(service_name, deploy_time, version);

-- Correlate logs with the most recent deployment version
SELECT
  l.log_time,
  l.service_name,
  l.log_level,
  l.message,
  d.version
FROM app_logs l
ASOF JOIN deployments d
  ON l.service_name = d.service_name
  AND l.log_time >= d.deploy_time
WHERE l.log_level = 'ERROR'
ORDER BY l.log_time DESC
LIMIT 20;

Advanced ASOF Join Techniques

1. Strict Forward Matching with >

Use > instead of >= to exclude exact timestamp matches:

SELECT *
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol
  AND t.trade_time > q.quote_time;  -- strictly greater than

2. Multi-Column Non-Equi Conditions

ASOF JOIN supports multiple non-equi conditions for complex scenarios:

-- Find the most recent record where price changed > 1%
SELECT *
FROM prices p1
ASOF JOIN prices p2
  ON p1.symbol = p2.symbol
  AND p1.ts > p2.ts
  AND ABS(p1.price - p2.price) / p2.price > 0.01;

3. Combining with Window Functions

-- Calculate running average spread before each trade
SELECT
  t.trade_id,
  t.trade_price,
  AVG(q.ask - q.bid) OVER (
    PARTITION BY t.symbol
    ORDER BY t.trade_time
  ) AS avg_spread_before_trade
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol
  AND t.trade_time >= q.quote_time;

Comparison Table: ASOF JOIN Across Tools

Monetization Strategies

Mastering DuckDB’s ASOF JOIN opens several revenue opportunities:

1. Quant Finance Consulting / Tooling

ASOF JOIN is the heart of financial data analysis. You can:

• Build real-time trade analytics pipelines for small hedge funds
• Develop a DuckDB-based backtesting engine to replace expensive Bloomberg/Wind terminals
• Project pricing: $2,000 - $10,000 per engagement

2. IoT Data Analytics Services

• Offer sensor data alignment & analysis services to manufacturing companies
• Build predictive maintenance dashboards
• Monthly retainer: $1,000 - $5,000 per client

3. Data Pipeline Optimization Consulting

• Help enterprises replace expensive ETL tools with DuckDB
• Optimize time-series query performance and reduce cloud data warehouse bills
• Hourly consulting: $150 - $400/hour

4. Online Courses & Content Monetization

• Publish DuckDB + time-series analysis tutorials on your blog/YouTube
• Create a premium course: “DuckDB Time-Series Analysis Bootcamp”
• Pricing: $49 - $199 per course

5. Open Source + Commercial Support

• Build and open-source a financial data toolkit powered by DuckDB ASOF JOIN
• Monetize via GitHub Sponsors or commercial support licenses

Summary

DuckDB v1.5.0’s ASOF JOIN is a breakthrough for time-series data analysis. It transforms what used to require complex self-joins and correlated subqueries into clean, declarative SQL. Whether you’re working on quantitative finance, IoT sensor data, or observability pipelines, ASOF JOIN dramatically improves both developer productivity and query performance.

For data engineers and analysts, mastering ASOF JOIN is quickly becoming an essential skill — especially when you need to find “the most recent match” across millions of time-series records in milliseconds.

Download DuckDB v1.5.0 today and leave your self-join nightmares behind:

# Install latest DuckDB CLI
pip install duckdb
# Or use the official installer
curl -fsSL https://install.duckdb.org | sh