Categories:: Query Syntax

ASOF JOIN¶

This topic describes how to use the ASOF JOIN construct in the FROM clause. An ASOF JOIN operation combines rows from two tables based on timestamp values that closely follow each other, precede each other, or match exactly. For a conceptual explanation of ASOF joins, see Joining Time-Series Data.

See also JOIN, which covers the syntax for other standard join types, such as inner and outer joins.

Syntax¶

The following FROM clause syntax is specific to ASOF JOIN:

SELECT ...
FROM <left_table> ASOF JOIN <right_table>
  MATCH_CONDITION (<left_table.timecol> <comparison_operator> <right_table.timecol>)
  [ ON <table.col> = <table.col> [ AND ... ] ]

Copy

SELECT ...

The SELECT list may select any and all columns from both tables. When there is no match for a row in the left table, the columns from the right table are null-padded.

FROM

The first (or left) table in the FROM clause is assumed to contain records that either follow (in time), precede, or are exactly synchronized with, the records in the second (or right) table.

In addition to regular tables and views, any object reference can be used in an ASOF JOIN. See FROM.

ASOF JOIN may be used in most contexts where joins are supported. See Usage Notes for some restrictions.

MATCH_CONDITION (left_table.timecol comparison_operator right_table.timecol)

This condition names the specific timestamp columns in each table that will be compared.

The order of tables is important in the condition. The left table must be named first.
The parentheses are required.
The comparison operator must be one of the following: >=, <=, >, <. The equals operator (=) is not supported.
All of the following data types are supported: DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ.
You can also use NUMBER columns in the match condition. For example, you might have NUMBER columns that contain UNIX timestamps (which define the number of seconds that have elapsed since January 1st, 1970).
The data types of the two matched columns do not have to be exactly the same, but they must be compatible.

ON table.col = table.col [ AND ... ]

The optional ON clause defines one or more equality conditions on columns in the two tables, for the purpose of logically grouping the results of the query.

The comparison operator must be the equal sign (=).
The ON clause cannot contain disjuncts (conditions connected with OR). Conditions connected with AND are supported.
Each side of a condition must refer to only one of the two tables in the join. However, the order of the table references does not matter.
Each ON condition may be enclosed in parentheses, but they are not required.

Usage notes¶

If no match is found in the right table for a given row, the result is null-padded for the selected columns from the right table. (ASOF joins are similar to left outer joins in this respect.)
If you use TIME columns in the match condition (as opposed to one of the timestamp types), you may need to set the TIME_OUTPUT_FORMAT parameter in order to see the exact TIME values that are being compared when you look at ASOF JOIN query results. By default, the display of a TIME column truncates milliseconds. See TIME Columns in the Match Condition.
More than one ASOF join can be used in the same query as long as all of the syntax rules are followed for each join. Each join must be immediately followed by its own MATCH_CONDITION. You cannot apply a single MATCH_CONDITION to multiple ASOF joins. See Multiple ASOF Joins in a Query.
ASOF joins are not supported for joins with LATERAL table functions or LATERAL inline views. For more information about lateral joins, see Lateral join.
An ASOF join with a self-reference is not allowed in a RECURSIVE common table expression (CTE). For information about CTEs, see WITH.
The EXPLAIN output for ASOF JOIN queries identifies the ON conditions and the MATCH_CONDITION. For example, in text or tabular format, you will see this kind of output above the table scans in the plan:
->ASOF Join joinKey: (S.LOCATION = R.LOCATION) AND (S.STATE = R.STATE), matchCondition: (S.OBSERVED >= R.OBSERVED)
Query profiles also clearly identify the ASOF JOIN operation in the plan. In this example, you can see that the table scan reads 22M rows from the left table, which are all preserved by the join. The profile also shows the match condition for the join.

More details on join behavior¶

The optional ON conditions for ASOF JOIN provide a way of grouping or partitioning table rows before the final matching rows are singled out by the required match condition. If you want the rows from the joined tables to be grouped on one or more dimensions that the tables share (stock symbol, location, city, state, company name, etc.), use an ON condition. If you do not use an ON condition, each row from the left table may be matched (by time) with any row from the right table in the final result set.

Here is an abstract example. Tables left_table and right_table have values A, B, etc. in column c1, and values 1, 2, etc. in column c2. Column c3 is a TIME column, and c4 is a numeric value (column of interest).

SELECT * FROM left_table ORDER BY c1, c2;

Copy

+----+----+----------+------+
| C1 | C2 | C3       |   C4 |
|----+----+----------+------|
| A  |  1 | 09:15:00 | 3.21 |
| A  |  2 | 09:16:00 | 3.22 |
| B  |  1 | 09:17:00 | 3.23 |
| B  |  2 | 09:18:00 | 4.23 |
+----+----+----------+------+

SELECT * FROM right_table ORDER BY c1, c2;

Copy

+----+----+----------+------+
| C1 | C2 | C3       |   C4 |
|----+----+----------+------|
| A  |  1 | 09:14:00 | 3.19 |
| B  |  1 | 09:16:00 | 3.04 |
+----+----+----------+------+

If c1 and c2 are both ON condition columns in the query, a row in the left table only matches a row in the right table when A and 1, A and 2, B and 1, or B and 2 are found in both tables. If no match is found for such values, the right table columns are null-padded.

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
    ON(l.c1=r.c1 and l.c2=r.c2)
  ORDER BY l.c1, l.c2;

Copy

+----+----+----------+------+------+------+----------+------+
| C1 | C2 | C3       |   C4 | C1   | C2   | C3       |   C4 |
|----+----+----------+------+------+------+----------+------|
| A  |  1 | 09:15:00 | 3.21 | A    |  1   | 09:14:00 | 3.19 |
| A  |  2 | 09:16:00 | 3.22 | NULL | NULL | NULL     | NULL |
| B  |  1 | 09:17:00 | 3.23 | B    |  1   | 09:16:00 | 3.04 |
| B  |  2 | 09:18:00 | 4.23 | NULL | NULL | NULL     | NULL |
+----+----+----------+------+------+------+----------+------+

If the ON conditions are removed, any combination of values in c1 and c2 may be matched in the final result. Only the match condition determines the results.

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
  ORDER BY l.c1, l.c2;

Copy

+----+----+----------+------+----+----+----------+------+
| C1 | C2 | C3       |   C4 | C1 | C2 | C3       |   C4 |
|----+----+----------+------+----+----+----------+------|
| A  |  1 | 09:15:00 | 3.21 | A  |  1 | 09:14:00 | 3.19 |
| A  |  2 | 09:16:00 | 3.22 | B  |  1 | 09:16:00 | 3.04 |
| B  |  1 | 09:17:00 | 3.23 | B  |  1 | 09:16:00 | 3.04 |
| B  |  2 | 09:18:00 | 4.23 | B  |  1 | 09:16:00 | 3.04 |
+----+----+----------+------+----+----+----------+------+

Using ASOF and MATCH_CONDITION as object names and aliases¶

This feature introduces two new keywords: ASOF and MATCH_CONDITION. The use of these keywords in SELECT command syntax is restricted:

If a SELECT statement uses ASOF or MATCH_CONDITION as the name of a table, view, or inline view, you must identify it as follows:
- If the object was created with double quotes in the name, use the same double-quoted name.
- If the object was created without double quotes in the name, use double quotes and capital letters.
For example, the following statements are no longer allowed and return errors:
```
SELECT * FROM asof;

WITH match_condition AS (SELECT * FROM T1) SELECT * FROM match_condition;
```
Copy
If you created the objects with double quotes, fix the problem by using double quotes:
```
SELECT * FROM "asof";

WITH "match_condition" AS (SELECT * FROM T1) SELECT * FROM "match_condition";
```
Copy
If you created the objects without double quotes, fix the problem by using double quotes and capital letters:
```
SELECT * FROM "ASOF";

WITH "MATCH_CONDITION" AS (SELECT * FROM T1) SELECT * FROM "MATCH_CONDITION";
```
Copy
If a SELECT statement uses ASOF or MATCH_CONDITION as an alias, you must use AS before the alias or double-quote the alias. For example, the following statements are no longer allowed and return errors:
```
SELECT * FROM t1 asof;

SELECT * FROM t2 match_condition;
```
Copy
Fix the problem in one of the following ways:
```
SELECT * FROM t1 AS asof;

SELECT * FROM t1 "asof";

SELECT * FROM t2 AS match_condition;

SELECT * FROM t2 "match_condition";
```
Copy

Examples¶

The following examples demonstrate the expected behavior of ASOF JOIN queries.

Join with match and ON conditions¶

The following example is described under Conceptual Example of an ASOF JOIN Query.

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time >= quote_time)
    ON t.stock_symbol=q.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW         | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW         | 2023-10-01 09:00:10.000 |     1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+

The ON condition groups the matched rows by their stock symbols.

NULL-padded results¶

As a follow-up to the previous example, insert a new row into the trades table with a date that is a day earlier than the existing rows in both trades and quotes:

INSERT INTO trades VALUES('SNOW','2023-09-30 12:02:55.000',3000);

Copy

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

Now run the first example query again. Note that the query returns four rows, but the new row is null-padded. There is no row in the quotes table that qualifies for the match condition. The columns from trades are returned, and the corresponding columns from quotes are null-padded.

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time >= quote_time)
    ON t.stock_symbol=q.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW         | 2023-09-30 12:02:55.000 |     3000 | NULL                    |         NULL |
| SNOW         | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW         | 2023-10-01 09:00:10.000 |     1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+

Using a different comparison operator in the match condition¶

Following on from the previous example, the results of the query change again when the comparison operator in the match condition is changed. The following query specifies the <= operator (instead of >=):

SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time <= quote_time)
    ON t.stock_symbol=q.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:07.000 | 142.00000000 |
| SNOW         | 2023-10-01 09:00:10.000 |     1500 | NULL                    |         NULL |
| SNOW         | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:07.000 | 166.00000000 |
| SNOW         | 2023-09-30 12:02:55.000 |     3000 | 2023-10-01 09:00:01.000 | 166.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+

Inner join to a third table¶

The following example adds a third companies table to the join in order to pick the company name for each stock symbol. You can use a regular INNER JOIN with an ON condition (or some other standard join syntax) to add the third table. However, note that USING(stock_symbol) would not work here because the reference to stock_symbol would be ambiguous.

SELECT t.stock_symbol, c.company_name, t.trade_time, t.quantity, q.quote_time, q.price
  FROM trades t ASOF JOIN quotes q
    MATCH_CONDITION(t.trade_time >= quote_time)
    ON t.stock_symbol=q.stock_symbol
    INNER JOIN companies c ON c.stock_symbol=t.stock_symbol
  ORDER BY t.stock_symbol;

Copy

+--------------+---------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | COMPANY_NAME  | TRADE_TIME              | QUANTITY | QUOTE_TIME              |        PRICE |
|--------------+---------------+-------------------------+----------+-------------------------+--------------|
| AAPL         | Apple Inc     | 2023-10-01 09:00:05.000 |     2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW         | Snowflake Inc | 2023-09-30 12:02:55.000 |     3000 | NULL                    |         NULL |
| SNOW         | Snowflake Inc | 2023-10-01 09:00:05.000 |     1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW         | Snowflake Inc | 2023-10-01 09:00:10.000 |     1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+---------------+-------------------------+----------+-------------------------+--------------+

Numbers as timestamps¶

The following example demonstrates that the match condition can compare numeric values. In this case, the tables have UNIX timestamp values stored in NUMBER(38,0) columns. 1696150805 is equivalent to 2023-10-30 10:20:05.000 (three seconds later than 1696150802).

SELECT * FROM trades_unixtime;

Copy

+--------------+------------+----------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY |        PRICE |
|--------------+------------+----------+--------------|
| SNOW         | 1696150805 |      100 | 165.33300000 |
+--------------+------------+----------+--------------+

SELECT * FROM quotes_unixtime;

Copy

+--------------+------------+----------+--------------+--------------+
| STOCK_SYMBOL | QUOTE_TIME | QUANTITY |          BID |          ASK |
|--------------+------------+----------+--------------+--------------|
| SNOW         | 1696150802 |      100 | 166.00000000 | 165.00000000 |
+--------------+------------+----------+--------------+--------------+

SELECT *
  FROM trades_unixtime tu
    ASOF JOIN quotes_unixtime qu
    MATCH_CONDITION(tu.trade_time>=qu.quote_time);

Copy

+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY |        PRICE | STOCK_SYMBOL | QUOTE_TIME | QUANTITY |          BID |          ASK |
|--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------|
| SNOW         | 1696150805 |      100 | 165.33300000 | SNOW         | 1696150802 |      100 | 166.00000000 | 165.00000000 |
+--------------+------------+----------+--------------+--------------+------------+----------+--------------+--------------+

TIME columns in the match condition¶

The following example joins two tables that contain weather observations. The observations in both tables are recorded into TIME columns. All the TIME values appear to be exactly the same in this result set (14:42:59).

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed>=s.observed);

Copy

+----------+----------+-------+-------------+----------+-----------+-------+-------------+
| OBSERVED | LOCATION | STATE | OBSERVATION | OBSERVED | LOCATION  | STATE | OBSERVATION |
|----------+----------+-------+-------------+----------+-----------+-------+-------------|
| 14:42:59 | Oakhurst | CA    |        0.50 | NULL     | NULL      | NULL  |        NULL |
| 14:42:59 | Ahwahnee | CA    |        0.90 | 14:42:59 | Fish Camp | CA    |        3.20 |
+----------+----------+-------+-------------+----------+-----------+-------+-------------+

To return a more precise display of TIME values, including milliseconds, run the following ALTER SESSION command, then run the join query again:

ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF3';

Copy

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed>=s.observed);

Copy

+--------------+----------+-------+-------------+--------------+-----------+-------+-------------+
| OBSERVED     | LOCATION | STATE | OBSERVATION | OBSERVED     | LOCATION  | STATE | OBSERVATION |
|--------------+----------+-------+-------------+--------------+-----------+-------+-------------|
| 14:42:59.001 | Oakhurst | CA    |        0.50 | NULL         | NULL      | NULL  |        NULL |
| 14:42:59.230 | Ahwahnee | CA    |        0.90 | 14:42:59.199 | Fish Camp | CA    |        3.20 |
+--------------+----------+-------+-------------+--------------+-----------+-------+-------------+

Multiple ASOF joins in a query¶

The following example shows how to connect a sequence of two or more ASOF joins in a single query block. The three tables (snowtime, raintime, preciptime) all contain weather observations that were recorded in specific locations at specific times. The column of interest is the observation column. The rows are logically grouped by state.

ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF3';

SELECT *
  FROM snowtime s
    ASOF JOIN raintime r
      MATCH_CONDITION(s.observed>=r.observed)
      ON s.state=r.state
    ASOF JOIN preciptime p
      MATCH_CONDITION(s.observed>=p.observed)
      ON s.state=p.state
  ORDER BY s.observed;

Copy

+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+
| OBSERVED     | LOCATION   | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION |
|--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------|
| 14:42:44.435 | Reno       | NV    |        3.00 | 14:42:44.435 | Reno     | NV    |        0.00 | 14:42:44.435 | Reno     | NV    |        0.01 |
| 14:42:45.000 | Bozeman    | MT    |        1.80 | NULL         | NULL     | NULL  |        NULL | 14:40:34.000 | Bozeman  | MT    |        1.11 |
| 14:42:59.199 | Fish Camp  | CA    |        3.20 | 14:42:59.001 | Oakhurst | CA    |        0.50 | 14:42:59.001 | Oakhurst | CA    |        0.51 |
| 14:43:01.000 | Lake Tahoe | CA    |        4.20 | 14:42:59.230 | Ahwahnee | CA    |        0.90 | 14:42:59.230 | Ahwahnee | CA    |        0.91 |
+--------------+------------+-------+-------------+--------------+----------+-------+-------------+--------------+----------+-------+-------------+

Less than and greater than comparison operators¶

Following on from the previous example, two ASOF joins are specified, but this time the first match condition uses the > operator and the second uses the < operator. The result is a single row that returns data from all three tables, and three rows that return data from two of the tables. Many of the columns in the result set are null-padded.

Logically, the query finds only one row where the observed time from the snowtime table was later than the observed time from the raintime table but earlier than the observed time from the preciptime table.

SELECT *
  FROM snowtime s
    ASOF JOIN raintime r
      MATCH_CONDITION(s.observed>r.observed)
      ON s.state=r.state
    ASOF JOIN preciptime p
      MATCH_CONDITION(s.observed<p.observed)
      ON s.state=p.state
    ORDER BY s.observed;

Copy

+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+
| OBSERVED     | LOCATION   | STATE | OBSERVATION | OBSERVED     | LOCATION  | STATE | OBSERVATION | OBSERVED     | LOCATION | STATE | OBSERVATION |
|--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------|
| 14:42:44.435 | Reno       | NV    |        3.00 | 14:41:44.435 | Las Vegas | NV    |        0.00 | NULL         | NULL     | NULL  |        NULL |
| 14:42:45.000 | Bozeman    | MT    |        1.80 | NULL         | NULL      | NULL  |        NULL | NULL         | NULL     | NULL  |        NULL |
| 14:42:59.199 | Fish Camp  | CA    |        3.20 | 14:42:59.001 | Oakhurst  | CA    |        0.50 | 14:42:59.230 | Ahwahnee | CA    |        0.91 |
| 14:43:01.000 | Lake Tahoe | CA    |        4.20 | 14:42:59.230 | Ahwahnee  | CA    |        0.90 | NULL         | NULL     | NULL  |        NULL |
+--------------+------------+-------+-------------+--------------+-----------+-------+-------------+--------------+----------+-------+-------------+

Examples of expected error cases¶

The following examples show queries that return expected syntax errors.

Having declared that snowtime s is the left table, you cannot begin the match condition with a reference to the right table, preciptime p:

SELECT * FROM snowtime s ASOF JOIN preciptime p MATCH_CONDITION(p.observed>=s.observed);

Copy

010002 (42601): SQL compilation error:
MATCH_CONDITION clause is invalid: The left side allows only column references from the left side table, and the right side allows only column references from the right side table.

Only the >=, <=, >, and < operators are allowed in match conditions:

SELECT * FROM preciptime p ASOF JOIN snowtime s MATCH_CONDITION(p.observed=s.observed);

Copy

010001 (42601): SQL compilation error:
MATCH_CONDITION clause is invalid: Only comparison operators '>=', '>', '<=' and '<' are allowed. Keywords such as AND and OR are not allowed.

The ON clause for ASOF JOIN must contain equality conditions:

SELECT *
  FROM preciptime p ASOF JOIN snowtime s
  MATCH_CONDITION(p.observed>=s.observed)
  ON s.state>=p.state;

Copy

010010 (42601): SQL compilation error:
ON clause for ASOF JOIN must contain conjunctions of equality conditions only. Disjunctions are not allowed. Each side of an equality condition must only refer to either the left table or the right table. S.STATE >= P.STATE is invalid.

An ON clause equality condition cannot contain disjunctions:

SELECT *
  FROM preciptime p ASOF JOIN snowtime s
  MATCH_CONDITION(p.observed>=s.observed)
  ON s.state=p.state OR s.location=p.location;

Copy

010010 (42601): SQL compilation error:
ON clause for ASOF JOIN must contain conjunctions of equality conditions only. Disjunctions are not allowed. Each side of an equality condition must only refer to either the left table or the right table. (S.STATE = P.STATE) OR (S.LOCATION = P.LOCATION) is invalid.

ASOF joins cannot be used with LATERAL inline views:

SELECT t1.a "t1a", t2.a "t2a"
  FROM t1 ASOF JOIN
    LATERAL(SELECT a FROM t2 WHERE t1.b = t2.b) t2
    MATCH_CONDITION(t1.a >= t2.a)
  ORDER BY 1,2;

Copy

010004 (42601): SQL compilation error:
ASOF JOIN is not supported for joins with LATERAL table functions or LATERAL views.