> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need. # Joins A join combines rows from two tables: blocks with their transactions, transactions with their logs, or one chain's blocks with another's. Tables can come from the same dataset or from different ones. [Batch queries](/querying/batch-queries) support every kind of join. [Streaming queries](/querying/streaming-queries) and [derived datasets](/datasets/derived-datasets), which run incrementally, support fewer. ## Prerequisites * A [connection](/querying/connecting) to a query server. The batch examples use `ampsql`, and the streaming ones the Python client from [Arrow Flight](/querying/arrow-flight#write-a-client), saved as `flight_query.py`. * To run the examples, the [example datasets](/querying/connecting#example-datasets). ## Which joins run where | Join | Batch queries | Streaming queries and derived datasets | | ----------------------------------------------------------------------- | ------------- | --------------------------------------------- | | `INNER JOIN` (or `JOIN`) | Yes | Yes, with [restrictions](/querying/joins#streaming-joins) | | `CROSS JOIN`, or tables separated by commas | Yes | Yes, with the same restrictions | | Semi joins: `IN` and `EXISTS` subqueries | Yes | Yes | | `LEFT SEMI JOIN`, `RIGHT SEMI JOIN` | Yes | No: write them as `IN` or `EXISTS` subqueries | | `LEFT`, `RIGHT`, and `FULL` outer joins | Yes | No | | Anti joins: `NOT IN`, `NOT EXISTS`, `LEFT ANTI JOIN`, `RIGHT ANTI JOIN` | Yes | No | Outer and anti joins return rows that have no match. A stream can't know a row has no match without remembering every earlier block, so Amp rejects them. ## Batch joins ### Inner joins An inner join returns the pairs of rows that match. Here, each block with its transactions: ```sql SELECT b.block_num, t.tx_hash, t.tx_index FROM "acme/eth_mainnet".blocks b INNER JOIN "acme/eth_mainnet".transactions t ON b.block_num = t.block_num ORDER BY t.block_num, t.tx_index LIMIT 3 ``` ```text [Output] +-----------+------------------------------------------------------------------+----------+ | block_num | tx_hash | tx_index | +-----------+------------------------------------------------------------------+----------+ | 24000000 | a94e96a83d0c8ec8726d5393b832f2973bdb16249f8c84b01672b5a150010836 | 0 | | 24000000 | 1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048 | 1 | | 24000000 | e182b514fa8ff30066186667135bd2bf8fc9bfb60618f9b3c9166253fac4249a | 2 | +-----------+------------------------------------------------------------------+----------+ ``` Joins chain: ```sql SELECT COUNT(*) AS total_logs FROM "acme/eth_mainnet".blocks b INNER JOIN "acme/eth_mainnet".transactions t ON b.block_num = t.block_num INNER JOIN "acme/eth_mainnet".logs l ON t.tx_hash = l.tx_hash ``` ```text [Output] +------------+ | total_logs | +------------+ | 223498 | +------------+ ``` ### Outer joins A left join keeps every row of the first table, with `NULL`s where the second has no match. Combined with `COUNT`, it counts zero for blocks without logs instead of leaving them out: ```sql SELECT b.block_num, COUNT(l.log_index) AS log_count FROM "acme/eth_mainnet".blocks b LEFT JOIN "acme/eth_mainnet".logs l ON b.block_num = l.block_num GROUP BY b.block_num ORDER BY b.block_num LIMIT 3 ``` ```text [Output] +-----------+-----------+ | block_num | log_count | +-----------+-----------+ | 24000000 | 1202 | | 24000001 | 812 | | 24000002 | 738 | +-----------+-----------+ ``` ### Semi and anti joins A semi join keeps the rows of a table that have a match in another, without repeating them for each match. An anti join keeps those that have none. Write them as subqueries: ```sql SELECT COUNT(*) AS txs_with_logs FROM "acme/eth_mainnet".transactions t WHERE t.tx_hash IN (SELECT tx_hash FROM "acme/eth_mainnet".logs) ``` ```text [Output] +---------------+ | txs_with_logs | +---------------+ | 10429 | +---------------+ ``` Or with `LEFT SEMI JOIN` and `LEFT ANTI JOIN`: ```sql SELECT COUNT(*) AS txs_without_logs FROM "acme/eth_mainnet".transactions t LEFT ANTI JOIN "acme/eth_mainnet".logs l ON t.tx_hash = l.tx_hash ``` ```text [Output] +------------------+ | txs_without_logs | +------------------+ | 6724 | +------------------+ ``` ### Joins across datasets Tables from different datasets join like any others. Here, Base blocks with the Ethereum block whose 12-second slot they fall in: ```sql SELECT e.block_num AS eth_block, COUNT(b.block_num) AS base_blocks, SUM(b.gas_used) AS base_gas FROM "acme/eth_mainnet".blocks e JOIN "acme/base_mainnet".blocks b ON b.timestamp >= e.timestamp AND b.timestamp < e.timestamp + INTERVAL '12 seconds' GROUP BY e.block_num ORDER BY e.block_num LIMIT 3 ``` ```text [Output] +-----------+-------------+-----------+ | eth_block | base_blocks | base_gas | +-----------+-------------+-----------+ | 24000000 | 6 | 334299428 | | 24000001 | 6 | 341687929 | | 24000002 | 6 | 382062502 | +-----------+-------------+-----------+ ``` Join chains by time, as here, rather than by block number: `a.block_num = b.block_num` pairs blocks that happen to have the same height on two chains, which says nothing about when they were produced. ### Column names A join's result can't have two columns with the same name. Tables of one dataset often share column names, like `block_num` and `timestamp`, so `SELECT *` over a join, or over tables separated by commas, fails: ```text Duplicate field names detected in plan schema: [acme/eth_mainnet.blocks._block_num and logs._block_num, …]. Please alias your columns to be unique. ``` Select the columns you need instead, with `AS` to rename the ones that clash, or one table's columns, like `logs.*`. For each row's block, use `block_num()`, since `_block_num` is ambiguous: ```sql SELECT block_num(), evm_decode_hex(logs.address) AS contract FROM "acme/eth_mainnet".blocks, "acme/eth_mainnet".logs AS logs WHERE logs.block_num = 24000000 LIMIT 1 ``` ```text [Output] +-------------+--------------------------------------------+ | block_num() | contract | +-------------+--------------------------------------------+ | 24000000 | 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 | +-------------+--------------------------------------------+ ``` ## Streaming joins (Experimental) In a streaming query, Amp joins the new rows of each microbatch with all the rows the other table already has, and sends the matches. The SQL is the same as in a batch query, with `SETTINGS stream = true`: :::terminal ```bash python flight_query.py 'SELECT b.block_num, b.gas_used, evm_decode_hex(t.tx_hash) AS first_tx FROM "acme/eth_mainnet".blocks b JOIN "acme/eth_mainnet".transactions t ON b.block_num = t.block_num WHERE t.tx_index = 0 AND b.block_num <= 24000002 SETTINGS stream = true' ``` ```text [mainnet 24000000-24000099] 3 rows block_num gas_used first_tx 24000000 57860235 0xa94e96a83d0c8ec8726d5393b832f2973bdb16249f8c84b01672b5a150010836 24000001 27660983 0x333c61f446a5f85fd18049bf3d867a62c3e5d19d43109b0b150f42e9954fdd4b 24000002 17602635 0x9dd4c9801f83d61b98adb4063447204f5d509520ac7caacfe91c52f910bc656e [mainnet 24000000-24000099] complete ``` ::: A derived dataset's SQL joins the same way, when its dataset is deployed. Later queries read the joined table, without running the join again. ### One general join per path Amp splits streaming joins in two: * **Block-aligned joins** match `_block_num` on both sides in a `USING` clause, like `JOIN logs l USING (_block_num, tx_hash)`. Rows can only match rows of the same block, so Amp only joins the new rows with each other, which is cheap. * **General joins** are every other inner, cross, or semi join, like `ON b.block_num = t.block_num`. Amp has to join the new rows of each side with all the old rows of the other. A query can have any number of block-aligned joins, but at most one general join on any chain of nested joins. Two stacked general joins fail with `query is not supported in streaming mode | Caused by: query contains non-incremental operation: StackedJoins`. To join more tables, add `_block_num` to a `USING` clause. Each log belongs to the same block as its transaction, so this query, with two block-aligned joins, gets every log of each block's first transaction: :::terminal ```bash python flight_query.py 'SELECT t.block_num, t.tx_index, l.log_index, evm_decode_hex(l.address) AS contract FROM "acme/eth_mainnet".blocks b JOIN "acme/eth_mainnet".transactions t USING (_block_num, block_num) JOIN "acme/eth_mainnet".logs l USING (_block_num, tx_hash) WHERE b.block_num = 24000001 AND t.tx_index = 0 SETTINGS stream = true' ``` ```text [mainnet 24000000-24000099] 3 rows block_num tx_index log_index contract 24000001 0 0 0x28d38df637db75533bd3f71426f3410a82041544 24000001 0 1 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 24000001 0 2 0xf7fd0860922bd3352e2dbaf725a182b74bf7a2e1 [mainnet 24000000-24000099] complete ``` ::: The same conditions in `ON`, like `ON t._block_num = l._block_num AND t.tx_hash = l.tx_hash`, make both joins general joins, and the query fails with `StackedJoins`. When the tables come from datasets of [several chains](/querying/streaming-queries#several-chains), Amp aligns them by time, and conditions on `_block_num` or `_ts` don't make a join block-aligned: count every join as a general join. ### Blocks of joined rows A joined row belongs to the later of the two blocks it was built from: its `block_num()` is the higher of the two rows'. That's the block range it's sent in, and the one a [reorg](/querying/streaming-queries#reorgs) of either block replaces. `SELECT *` fails in a streaming join, like in a batch one, and so does selecting one table's columns, like `logs.*`, which a batch join allows. `_block_num` and `_ts` are ambiguous: ``selecting `_ts` from a multi-table context (e.g. a join) is ambiguous``. Select columns by name, and use `block_num()` and `ts()`. ### Joins across chains Streaming joins work across chains. Here, the Base block produced at the same second as each Ethereum block: :::terminal ```bash python flight_query.py 'SELECT e.block_num AS eth_block, b.block_num AS base_block, e.timestamp FROM "acme/eth_mainnet".blocks e JOIN "acme/base_mainnet".blocks b ON e.timestamp = b.timestamp WHERE e.block_num <= 24000002 SETTINGS stream = true' ``` ```text [base 39397512-39398106, mainnet 24000000-24000099] 3 rows eth_block base_block timestamp 24000000 39397512 2025-12-13 00:06:11+00:00 24000001 39397518 2025-12-13 00:06:23+00:00 24000002 39397524 2025-12-13 00:06:35+00:00 [base 39397512-39398106, mainnet 24000000-24000099] complete ``` :::