SQL & APIs · Talking to data
SQL: SELECT, WHERE, JOIN
The language of every trade database. Reconcile two tables in one query.
Step 1 of 4 · Overview15 min read ·
SELECT o.id, o.qty AS our_qty, t.qty AS their_qty
FROM ours o
FULL OUTER JOIN theirs t ON o.id = t.id
WHERE o.id IS NULL -- missing in ours
OR t.id IS NULL -- missing at counterparty
OR o.qty <> t.qty; -- qty break| JOIN | Keeps |
|---|---|
| INNER | only ids in both tables |
| LEFT | all of ours, matches from theirs (NULL if none) |
| FULL OUTER | everything from both sides |
FROM/JOIN
pick tables
WHERE
filter rows
GROUP BY
bucket
HAVING
filter buckets
SELECT
choose columns
ORDER BY
sort
Next: Practice