Foreign key
A column that points to the primary key of another table, such as buyer_id in orders pointing to buyers. It is how tables connect in a join.
Formulachild.key → parent.primary key
How we work it through
- 1
Find which column points where: orders.buyer_id → buyers.buyer_id.
- 2
Check for orphans in both directions before a join.
- 3
Decide whether rows without a partner must stay (LEFT) or go (INNER).
On the practice dataset
The query we run on Meridian, Atterna’s practice marketplace, and what it returns.
SELECT COUNT(*) AS buyers,
COUNT(*) FILTER (WHERE buyer_id IN
(SELECT buyer_id FROM meridian.orders)) AS with_orders
FROM meridian.buyers;Result7,000 buyers, 3,705 of them appear in orders — the other 3,295 never ordered
The common trap
Joining on a column that repeats on both sides, which multiplies rows.
When an AI answers
An AI assistant
“All 7,000 buyers have ordered.”
- What it didn’t know
- It LEFT JOINed buyers to orders and counted b.buyer_id, which is filled for every buyer, matched or not.
- Ask back
- “How many buyers have no row in orders?”
Read next
Examples use Meridian, Atterna’s fictional practice marketplace.