SQL ASOF JOIN explained: match each row to the latest value, in every engine

A normal join matches rows when keys are equal. Many real questions need something else: the row with the latest timestamp at or before another timestamp. The price of a share at the moment of a trade. The exchange rate in force on the day of an invoice. The customer's plan when they opened a support ticket. That is an ASOF JOIN ("as of" this time). Snowflake, DuckDB, ClickHouse and Polars have it, and Apache Spark added it in version 4.3. This guide explains the idea once, then shows the syntax in each tool.

The idea, with one example

We will use the same two small tables everywhere. Each trade should get the latest quote for the same symbol at or before the trade time.

Notice trade 2: there is also a quote at 10:00:25, but it came after the trade, so it must not be used. And trade 3 has no earlier quote at all. What happens to it depends on the join type, which is the most common source of bugs.

The syntax in each engine

Every example below returns the prices 10.0, 10.5, NULL and 20.0. We ran the DuckDB 2.0, Spark 4.3, Polars 2.1 and pandas 3.1 versions ourselves.

SELECT t.trade_id, t.sym, t.ts, q.price
FROM trades t
ASOF JOIN quotes q
  MATCH_CONDITION (t.ts >= q.ts)
  ON t.sym = q.sym;

SELECT t.trade_id, t.sym, t.ts, q.price
FROM trades t
ASOF LEFT JOIN quotes q
  ON t.sym = q.sym AND t.ts >= q.ts;

SET spark.sql.join.asofJoin.enabled = true;

SELECT t.trade_id, q.price
FROM trades AS t
LEFT ASOF JOIN quotes AS q
  MATCH_CONDITION (t.ts >= q.ts)
  ON t.sym = q.sym;

import polars as pl

# DataFrame API: both frames sorted by the time column
result = trades.sort("ts").join_asof(
    quotes.sort("ts"), on="ts", by="sym", strategy="backward"
)

# SQL, Snowflake style (Polars 2.1+)
pl.SQLContext(trades=trades, quotes=quotes).execute("""
    SELECT trades.trade_id, price
    FROM trades
    ASOF JOIN quotes MATCH_CONDITION (trades.ts >= quotes.ts)
    ON trades.sym = quotes.sym
""").collect()

import pandas as pd

result = pd.merge_asof(
    trades.sort_values("ts"),
    quotes.sort_values("ts"),
    on="ts", by="sym",
    direction="backward",                # latest at or before
    tolerance=pd.Timedelta("10s"),       # optional: ignore stale quotes
)

SELECT t.trade_id, q.price
FROM trades AS t
ASOF LEFT JOIN quotes AS q
  ON t.sym = q.sym AND t.ts >= q.ts;

No ASOF JOIN? Use this fallback

BigQuery and PostgreSQL have no ASOF keyword, but two patterns give the same answer. Both returned identical results to the ASOF versions in our tests.

-- QUALIFY version (WHERE TRUE is harmless and keeps BigQuery happy,
-- which has long asked for WHERE, GROUP BY or HAVING next to QUALIFY)
SELECT t.trade_id, q.price
FROM trades t
LEFT JOIN quotes q
  ON q.sym = t.sym AND q.ts <= t.ts
WHERE TRUE
QUALIFY ROW_NUMBER() OVER (PARTITION BY t.trade_id ORDER BY q.ts DESC) = 1;

-- LATERAL version (PostgreSQL)
SELECT t.trade_id, q.price
FROM trades t
LEFT JOIN LATERAL (
  SELECT price FROM quotes q
  WHERE q.sym = t.sym AND q.ts <= t.ts
  ORDER BY q.ts DESC
  LIMIT 1
) q ON TRUE;

Four mistakes to avoid

More SQL: SQL joins explained, window functions explained and the SQL cheat sheet. On the tools: what's new in Polars 2.1 and DuckDB 2.0.

Sources: Snowflake documentation for ASOF JOIN; DuckDB, ClickHouse, pandas (merge_asof) and Polars (join_asof) documentation; Apache Spark 4.3.0 release notes (30 October 2026). Examples tested with DuckDB 2.0.0, PySpark 4.3.0, Polars 2.1.0 and pandas 3.1.0; Snowflake, ClickHouse, BigQuery and PostgreSQL syntax follows their documentation.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 9 November 2026. About me