JOINs
without
surprises.

SELECT a.account_id,
       COUNT(i.invoice_id) AS invoice_matches
FROM accounts a
LEFT JOIN invoices i ON i.account_id = a.account_id
GROUP BY a.account_id
ORDER BY a.account_id;

accounts has one row per account and invoices has one per invoice. The GROUP BY sets the result grain to one row per account. Counting the non-null invoice key preserves Dee with zero matches.

SELECT COUNT(*) AS joined_rows,
       SUM(i.total) AS joined_total
FROM invoices i
JOIN account_import a
  ON a.account_id = i.account_id;

SELECT account_id, COUNT(*) AS rows_per_key
FROM account_import
GROUP BY account_id
HAVING COUNT(*) > 1;

The repeated account_id = 10 pairs both Ari invoices with two lookup rows. Four matching invoices become six joined rows and the joined total rises from 550 to 720. The second query identifies the duplicated lookup key.

-- Matching account/invoice pairs only: 4 rows
SELECT COUNT(*)
FROM accounts a JOIN invoices i
  ON i.account_id = a.account_id;

-- Keep every account: 5 rows
SELECT COUNT(*)
FROM accounts a LEFT JOIN invoices i
  ON i.account_id = a.account_id;

-- Build a deliberate 2 x 2 grid: 4 rows
SELECT *
FROM (VALUES (DATE '2026-01-10'), (DATE '2026-01-11')) d(flow_date)
CROSS JOIN (VALUES (10), (20)) a(account_id);

INNER keeps matches. LEFT preserves the account side. CROSS creates every possible pair. FULL OUTER is reserved for reconciliation when unmatched rows from either input must survive.

SELECT m.member_id, m.name
FROM members m
LEFT JOIN logins l
  ON l.member_id = m.member_id
 AND l.logged_at >= DATE '2026-07-01'
WHERE l.login_id IS NULL;

The date belongs in ON because it defines a recent-login match. Testing the non-null login key proves that no qualifying login row exists.

WITH fill_totals AS (
  SELECT order_id, SUM(quantity) AS filled_quantity
  FROM fills
  GROUP BY order_id
)
SELECT o.order_id, o.quantity, f.filled_quantity
FROM orders o
JOIN fill_totals f ON f.order_id = o.order_id
WHERE f.filled_quantity > o.quantity;

fill_totals is one row per order. The later join therefore cannot repeat an order-level quantity once per raw fill.

SELECT a.account_id,
       COUNT(*) AS joined_rows,
       COUNT(i.invoice_id) AS matched_invoices,
       COUNT(*) - COUNT(i.invoice_id) AS missing_invoices
FROM accounts a
LEFT JOIN invoices i ON i.account_id = a.account_id
GROUP BY a.account_id;

The unmatched account owns one output row, zero non-null invoice keys, and therefore one missing relationship.

SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.user_id = u.user_id AND p.status = 'completed'
)
AND NOT EXISTS (
  SELECT 1 FROM refunds r WHERE r.user_id = u.user_id
);

Membership tests keep each user once regardless of how many payment rows match. EXISTS checks for a completed payment, while NOT EXISTS checks for any refund.

SELECT t.trade_id, base.name, quote.name
FROM trades t
JOIN assets base  ON base.symbol = t.base_asset
JOIN assets quote ON quote.symbol = t.quote_asset
ORDER BY t.trade_id;

SELECT DISTINCT d.user_id
FROM deposits d
JOIN trades t
  ON t.user_id = d.user_id
 AND t.executed_at::DATE = d.completed_at::DATE
WHERE d.status = 'completed'
ORDER BY d.user_id;

Aliases describe two roles played by the same table. The second relationship needs both user and calendar day; remove either and unrelated facts can pair. Both predicates are required, but neither alone promises one-to-one cardinality.

WITH expected AS (
  SELECT p.pair_id, c.trade_date
  FROM pairs p
  CROSS JOIN (
    SELECT DATE '2026-01-01' + CAST(i AS INTEGER) AS trade_date
    FROM range(0, 3) t(i)
  ) c
  WHERE c.trade_date >= p.listed_at
)
SELECT e.pair_id, e.trade_date
FROM expected e
LEFT JOIN candles c
  ON c.pair_id = e.pair_id
 AND c.trade_date = e.trade_date
WHERE c.pair_id IS NULL
ORDER BY e.pair_id, e.trade_date;

The generated calendar and pairs create the rows that should exist. Any expected row left unmatched by the anti-join is a missing candle.

WITH dep AS (
  SELECT completed_at AS flow_date,
         SUM(usd_value) AS deposits_usd
  FROM cash_deposits
  WHERE status = 'completed'
  GROUP BY completed_at
), wd AS (
  SELECT completed_at AS flow_date,
         SUM(usd_value) AS withdrawals_usd
  FROM cash_withdrawals
  WHERE status = 'completed'
  GROUP BY completed_at
)
SELECT COALESCE(dep.flow_date, wd.flow_date) AS flow_date,
       COALESCE(dep.deposits_usd, 0)
         - COALESCE(wd.withdrawals_usd, 0) AS net_flow_usd
FROM dep
FULL OUTER JOIN wd ON wd.flow_date = dep.flow_date
ORDER BY flow_date;

SELECT v.user_id, v.volume, f.tier_name
FROM user_volume v
JOIN fee_tiers f
  ON v.volume >= f.min_volume
 AND (v.volume < f.max_volume OR f.max_volume IS NULL)
ORDER BY v.user_id;

FULL OUTER JOIN preserves a date found on either side. The half-open range assigns the exact boundary of 100 to the next tier once, not twice.