---
title: "ASOF JOIN"
description: "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."
canonical: "https://motherduck.com/glossary/asof-join/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "DuckDB vs Pandas vs Polars for Python Developers"
    url: "https://motherduck.com/blog/duckdb-versus-pandas-versus-polars/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Syntax
```sql
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 `>=`):

```sql
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.