ASOF JOIN
An ASOF JOIN matches each row in one table to the closest preceding (or following) row in another table based on an ordering column, commonly a timestamp -- a core building block for time-series analysis.
Overview
ASOF JOIN matches rows between two tables based on the closest value of an ordering column -- typically a timestamp -- rather than requiring an exact match. It's purpose-built for time-series data, where you often need to answer questions like "what was the most recent stock price at or before this trade happened?" without an exact timestamp match ever existing between the two tables.
DuckDB is one of the few general-purpose analytical databases with native ASOF JOIN support, making it a genuine strength for time-series workloads that would otherwise require slow, manual self-joins or window function workarounds in other engines.
Syntax
Copy code
SELECT h.ticker, h.when, p.price * h.shares AS value
FROM holdings h
ASOF JOIN prices p
ON h.ticker = p.ticker AND h.when >= p.when;
The inequality condition (here h.when >= p.when) is required and determines the matching direction; any other conditions in the ON clause must be equalities. For each row in holdings, DuckDB finds the single prices row with the largest p.when that is still <= the holding's timestamp -- the most recent price known at that point in time.
USING shorthand
When the join and ordering columns share the same name on both sides, USING is more concise, with the last column listed treated as the inequality (matched as >=):
Copy code
SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");
Note that with USING and SELECT *, output columns come from the left (probe) side for the matched keys -- to pull columns from the right side, reference them explicitly.
LEFT ASOF JOIN
Like other join types, ASOF JOIN supports a LEFT variant (ASOF LEFT JOIN) that keeps unmatched left-side rows, filling right-side columns with NULL when no earlier/later row exists to match against.
Related terms
How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
CROSS JOIN →A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second.
Time-series database →A time-series database is a database optimized for storing and querying data points indexed by time, such as metrics, sensor readings, or logs, with efficient time-range scans, downsampling, and retention.
SELF JOIN →A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other.
FAQS
It matches rows based on the closest value of an ordering column (usually a timestamp) rather than an exact match, which is exactly what's needed to align two time series that were sampled at different, non-matching intervals.
At least one inequality condition on the ordering column, typically >=, with any other conditions in the ON clause being equalities.
By default (a plain ASOF JOIN, an inner join), rows with no matching earlier row are dropped; use ASOF LEFT JOIN to keep all left-side rows and fill unmatched right-side columns with NULL.
