A database join combines rows from two tables according to a matching condition. In SQL, joining holdings to prices can attach a valuation mark to each position. The crucial question is how many prices each holding is allowed to match: a query can execute successfully while counting the same economic position several times.
A join returns matching pairs
Suppose a fictional portfolio has three lots, all measured in units: lot 1 holds 2 of instrument A, lot 2 holds 3 of B, and lot 3 holds 1 of A. The price snapshot contains one USD mark per instrument: A at 100 and B at 50.
An inner join on instrument returns three pairs. The two A lots remain separate; each receives the A price. The total is USD.
Now add another A quote at 101 without removing the first. Each A lot matches both A quotes. The result contains five rows, with amounts 200, 202, 150, 100 and 101 USD. Summing gives 753 USD. That is the sum of the matching pairs, but it is not a valid latest-price portfolio valuation. If 101 is the eligible latest A mark, the valuation is USD.
Fictional holdings and USD prices. Each A lot is repeated when it matches two A quotes; join multiplicity changes the aggregate.
For a non-null equality key appearing times on the left and times on the right, that key contributes inner-join rows. Here A contributes and B contributes . This describes the result, not a requirement that the database physically enumerate every possible pair. PostgreSQL's joined-table documentation specifies the matching-pair semantics.
Execute the mistake, then inspect it
This self-contained Python example runs real SQLite queries. Prices are integer USD per unit to keep the arithmetic exact; the example does not model fractional currency amounts.
import sqlite3
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE holdings (
lot_id INTEGER PRIMARY KEY, instrument TEXT NOT NULL, units INTEGER NOT NULL
);
CREATE TABLE quotes (
quote_id INTEGER PRIMARY KEY, instrument TEXT NOT NULL,
quote_time TEXT NOT NULL, usd INTEGER NOT NULL
);
""")
connection.executemany("INSERT INTO holdings VALUES (?, ?, ?)",
[(1, "A", 2), (2, "B", 3), (3, "A", 1)])
connection.executemany("INSERT INTO quotes VALUES (?, ?, ?, ?)",
[(1, "A", "2026-10-27T09:00:00Z", 100),
(2, "B", "2026-10-27T09:00:00Z", 50)])
query = """
SELECT h.lot_id, h.instrument, h.units, q.usd, h.units * q.usd
FROM holdings AS h INNER JOIN quotes AS q
ON h.instrument = q.instrument
ORDER BY h.lot_id, q.quote_id
"""
rows = connection.execute(query).fetchall()
print(rows)
print(len(rows), sum(row[4] for row in rows))
# [(1, 'A', 2, 100, 200), (2, 'B', 3, 50, 150), (3, 'A', 1, 100, 100)]
# 3 450
connection.execute("INSERT INTO quotes VALUES (?, ?, ?, ?)",
(3, "A", "2026-10-27T10:00:00Z", 101))
rows = connection.execute(query).fetchall()
print(len(rows), sum(row[4] for row in rows))
# 5 753
connection.close()ON defines the match; ORDER BY only makes the display order reproducible. Sorting prices by time does not select one price per instrument. Python's sqlite3 tutorial explains the connection, parameter placeholders and result tuples used here.The quote IDs are unique, yet instrument A still has two matches. A row identifier and a join key solve different problems.
Decide what an unmatched row means
| Join type | Rows retained | Typical quant use |
|---|---|---|
INNER JOIN | Every matching pair | Restrict analysis to matched records, with exclusions measured |
LEFT JOIN | Every matching pair, plus each unmatched left row with right columns set to NULL | Keep every holding visible while checking price coverage |
RIGHT JOIN | Symmetric to a left join | Preserve the right-hand universe |
FULL OUTER JOIN | Matching pairs and unmatched rows from both sides | Reconcile two inventories |
CROSS JOIN | Every left-right combination | Intentionally build a scenario grid |
These are result rules, not guarantees of one output row per input row. An outer join still repeats a holding if multiple quotes match it. The PostgreSQL table-expression reference also distinguishes these join types.
If a holding in C has no quote, an inner join omits it. A left join retains it with
NULL price and NULL value: missing is not zero. Under ordinary SQL equality, a null key does not match another null key. Also, putting WHERE q.usd > 0 after a left join removes the unmatched rows because the test on their null prices is not true. Audit missing matches before accepting the valuation. SQLite's SELECT reference documents outer-row construction and filtering.Write the key and time contract first
For this valuation, the intended relationship is many holdings to one eligible price. Establish that relationship before aggregation:
- Identify an instrument unambiguously. A ticker alone may omit exchange, currency or share class.
- Define one row's meaning on each side. A quote history can legitimately have many rows per instrument; a valuation snapshot should resolve them to one eligible mark.
- Specify valuation time, quote field, source, currency, revision rules and maximum permitted age. Resolve equal-time ties explicitly. For historical research, require that the mark was available at the decision time to avoid look-ahead bias.
- Enforce the intended non-null unique key where possible, and check unmatched rows and matches per holding after joining. A unique
(instrument, quote_time)pair does not make instrument alone unique.
For the fixture, a cutoff of 09:30 UTC admits the 09:00 marks and gives 450 USD. A cutoff of 10:00 UTC with latest-at-or-before selection admits the 10:00 A mark and gives 453 USD. Joining every quote with time less than or equal to the cutoff would still retain both A quotes at 10:00; the time inequality alone does not choose the latest one.
Database
UNIQUE and NOT NULL constraints can enforce a chosen snapshot key. Their treatment of nullable keys deserves explicit attention; uniqueness alone need not exclude multiple null keys. See the checked PostgreSQL constraint documentation and SQLite table constraints.Why DISTINCT is not a repair
SELECT DISTINCT removes identical selected result rows. The A prices 100 and 101 differ, so both pairings survive when the quote or value is selected. If you omit the price and select only the instrument, DISTINCT can instead collapse legitimate separate lots. It cannot decide which source, timestamp or revision belongs to a valuation. Correct the pairing rule, then aggregate. SQLite documents DISTINCT separately from join processing.For the schema side of this problem, SQL and Persistent Data teaches composite price keys, duplicate rejection and bounded queries. The full lesson requires membership; its public preview is available without the full lesson.
Sources
- SQLite: SELECT, sections 2.1, 2.3, 2.6 and 4: joins, filtering, duplicate removal and explicit ordering.
- PostgreSQL 18: Table Expressions, section 7.2.1.1: join types and the difference between
ONandWHEREfor outer joins. - PostgreSQL 18: Constraints, sections 5.5.2–5.5.4: non-null, unique and primary-key contracts.
- SQLite: CREATE TABLE, sections 3.5–3.8: key, unique and non-null constraints.
- Python 3.14: sqlite3, tutorial and placeholders. Example executed with Python 3.14.4; SQLite 3.53.0.