- INNER JOIN
- ON clause
- composite join key
- table aliases
- slippage
- enrichment
JOINs — Where Two Financial Tables Meet 🔗
Wednesday, post-close. Your head of trading drops a CSV on your desk: "Compliance wants slippage on every fill today — how far each trade printed from the day's official close. Numbers by 9am." The fills live in one table (trade_ledger). The closing prices live in another (equity_prices). Neither table alone can answer the question. You need to stitch them together — one trade to its own day's closing price — and that stitch is a JOIN.
Get the stitch right and slippage falls out in one query. Get it wrong and you will silently compare Apple's fill to JPMorgan's close, or today's trade to last March's price — and hand compliance a number that is confidently, dangerously wrong.
🧠 The mental model: matching two decks of cards
Picture two decks face-up on the table. The left deck is your trades (one card per fill). The right deck is your prices (one card per ticker per day). A JOIN walks the left deck and, for each trade card, hunts the right deck for the card that matches.
The ON clause is the matching rule — it says what "the same card" means. And here is the part finance students always underestimate: matching on ticker alone is not enough. AAPL appears on ~520 price cards (one per trading day). To pin a trade to its price you must match on both the ticker and the date. That pair — (ticker, trade_date) — is a composite key, and it is the whole lesson.
| ON clause | What it matches | Result |
|---|---|---|
ON t.ticker = e.ticker | every AAPL trade to every AAPL day | ~520× too many rows (a "fan-out") |
ON t.trade_date = e.trade_date | every trade to every ticker's price that day | wrong ticker's price |
ON t.ticker = e.ticker AND t.trade_date = e.trade_date | each trade to its own day's price | ✅ exactly one match |
What INNER JOIN does, step by step
It helps to see the machine work. INNER JOIN conceptually does three things:
- ›Pair up every left row with every right row (a giant grid of possibilities).
- ›Keep only the pairs where your
ONrule is true. - ›Return the surviving pairs as one wide row each.
So the ON clause is a filter on pairings. Loosen it (fewer conditions) and more pairings survive — that is the fan-out. Tighten it to the true composite key and exactly the right pairings survive: one price per trade.
INNER JOIN vs LEFT JOIN
| Type | Keeps a trade if… |
|---|---|
INNER JOIN | …a matching price row exists (unmatched trades vanish) |
LEFT JOIN | …always — unmatched trades stay, price columns come back NULL |
We use INNER JOIN here: a trade booked on a day with no closing price (e.g. a weekend or holiday timestamp) has no slippage to compute, so dropping it is exactly what we want. Reach for LEFT JOIN instead when the unmatched rows are the story — for example, an audit that must flag trades with no corresponding market print.
A worked pairing
Say the ledger has one row — AAPL, 2022-06-24, filled at 155.10 — and the price table holds AAPL's close of 153.20 for that same date. With the composite key, those two cards match and the join emits a single enriched row:
| trade_id | ticker | trade_price | market_close | slippage |
|---|---|---|---|---|
| … | AAPL | 155.10 | 153.20 | +1.90 |
Here the fill printed 1.90 above that day's close — the gap a Transaction Cost Analysis report calls slippage. (Heads-up: the sandbox seeds simulated fill prices that are not tied to each stock's real level, so your gaps will look large and random — treat the numbers as placeholders. What matters in this lesson is the shape: the right ticker, matched to its own day's close, with a computed gap column.)
Reading the query
SQLFROM trade_ledger t -- alias the long table names JOIN equity_prices e -- 't' = trades, 'e' = equity prices ON t.ticker = e.ticker -- match the SAME stock… AND t.trade_date = e.trade_date -- …on the SAME day
The aliases t and e are just nicknames so you can write t.price instead of trade_ledger.price. The gap itself is simply trade_price − market_close: a positive value means the fill printed above the day's close, negative means it printed below. (A real desk flips the sign by side — printing above the close is a cost when you are buying but a gain when you are selling — but here we show the raw price gap.)
💼 Career note: Measuring slippage (fill vs. close) is the core of Transaction Cost Analysis (TCA). Every buy-side desk reports it; a fund that ignores it is leaking basis points to the market on every order.
Your Task
The starter is a complete slippage query with one clause sabotaged. It already matches on ticker, but the second half of the ON — the date match — has been replaced with a placeholder string that matches nothing. Run it as-is and you will get the right column headers but zero rows: every trade fails to find a price, so INNER JOIN keeps none of them.
Your job: fix the ON clause so each trade lines up with the price on its own trade_date.
Predict before you run: the placeholder is t.trade_date = 'PUT_THE_JOIN_KEY_HERE' — a constant that no real date equals. Before touching it, ask yourself: why does a constant on the right side of = make the INNER JOIN return empty rather than error out? Then replace it with the column that makes the match real, and watch 10 rows appear.