Join Operations

Combine data from multiple tables including AVA's powerful ASOF JOIN for time-series data.

INNER JOIN

Returns rows with matching values in both tables.

-- Implicit inner join
SELECT p.*, c.company_name
FROM prices p, companies c
WHERE p.TICKER = c.TICKER;

-- Explicit inner join
SELECT p.TICKER, p.DATE, p.PRICE, c.company_name, c.sector
FROM prices p
INNER JOIN companies c ON p.TICKER = c.TICKER;

LEFT JOIN

Returns all rows from left table, with matching rows from right table (NULL if no match).

-- Left join with multiple conditions
SELECT
    p.TICKER,
    p.DATE,
    p.PRICE,
    d.dividend_amount
FROM prices p
LEFT JOIN dividends d
    ON p.TICKER = d.TICKER
   AND p.DATE = d.ex_date;

-- Left join with aggregation
SELECT
    p.TICKER,
    COUNT(*) as price_records,
    COUNT(d.dividend_amount) as dividend_records
FROM prices p
LEFT JOIN dividends d ON p.TICKER = d.TICKER
GROUP BY p.TICKER;

OUTER JOIN

Returns all rows from both tables, with NULL where no match exists.

-- Full outer join
SELECT
    COALESCE(p.TICKER, c.TICKER) as TICKER,
    p.DATE,
    p.PRICE,
    c.company_name
FROM prices p
OUTER JOIN companies c ON p.TICKER = c.TICKER;

CROSS JOIN

Cartesian product - every row from first table paired with every row from second table.

-- Create all ticker-date combinations
SELECT * FROM
    (SELECT DISTINCT TICKER FROM prices)
CROSS
    (SELECT DISTINCT DATE FROM prices);

-- Generate complete time series
CREATE TABLE all_ticker_dates AS (
    SELECT TICKER, DATE
    FROM (SELECT DISTINCT TICKER FROM prices) tickers
    CROSS (SELECT DATE FROM trade_dates) dates
);

ASOF JOIN - Time-Series Join

AVA Database Unique Feature

ASOF JOIN matches each row with the most recent previous row from the right table where the time column is less than or equal to the left table's time. Perfect for point-in-time lookups and time-series data.

Basic ASOF JOIN

-- Join prices with dividends (point-in-time)
SELECT
    p.TICKER,
    p.DATE,
    p.PRICE,
    d.dividend_amount
FROM prices p
ASOF JOIN dividends d
    ON p.TICKER = d.TICKER
   AND p.DATE = d.announce_date;

-- Join with corporate actions
SELECT
    p.*,
    s.split_ratio
FROM prices p
ASOF JOIN splits s
    ON p.TICKER = s.TICKER
   AND p.DATE = s.effective_date;

ASOF JOIN with Point-in-Time Data

-- Get most recent earnings estimate as of each date
SELECT
    p.TICKER,
    p.DATE,
    p.PRICE,
    e.eps_estimate,
    e.estimate_date
FROM prices p
ASOF JOIN earnings_estimates e
    ON p.TICKER = e.TICKER
   AND p.DATE = e.estimate_date;

-- Join with fundamentals (get latest known values)
SELECT
    p.TICKER,
    p.DATE,
    p.PRICE,
    f.revenue,
    f.earnings,
    f.report_date
FROM prices p
ASOF JOIN fundamentals f
    ON p.TICKER = f.TICKER
   AND p.DATE = f.report_date;

ASOF JOIN Use Cases

1. Corporate Actions Adjustment

Apply split and dividend adjustments to historical prices based on effective dates.

2. Point-in-Time Fundamentals

Join price data with the most recent known fundamental data as of each trading date.

3. Event Studies

Analyze price movements around corporate events (earnings, announcements, etc.).

4. Analyst Estimates

Match each trading day with the most recent analyst consensus estimates.

Complex ASOF JOIN Example

-- Comprehensive point-in-time analysis
SELECT
    p.TICKER,
    p.DATE,
    p.PRICE,
    -- Latest earnings
    e.eps as latest_eps,
    e.report_date,
    -- Latest split adjustment
    s.split_ratio,
    s.effective_date as split_date,
    -- Latest dividend
    d.dividend_amount,
    d.ex_date as dvd_date
FROM prices p
-- Join with earnings
ASOF JOIN earnings e
    ON p.TICKER = e.TICKER
   AND p.DATE = e.report_date
-- Join with splits
ASOF JOIN splits s
    ON p.TICKER = s.TICKER
   AND p.DATE = s.effective_date
-- Join with dividends  
ASOF JOIN dividends d
    ON p.TICKER = d.TICKER
   AND p.DATE = d.ex_date
WHERE p.DATE >= '2025-01-01';

Table Lookup Syntax

AVA's unique [key] syntax for efficient table lookups in UPDATE statements.

-- Update with lookup from another table
UPDATE company_name: companies[TICKER].company_name
FROM prices;

-- Multiple key lookup
UPDATE factor_value: factors[TICKER, DATE].value
FROM portfolio;

-- Conditional lookup
DELETE FROM prices
WHERE exclusions[TICKER].is_excluded IS NOT NULL;