query-sql

query-sql

Never write SQL against a schema you haven't inspected. The failure mode is a confidently-wrong query on guessed column names. Discover first, explore with guardrails, then run the real thing.

1. Discover the schema first

List tables, then describe the ones you'll touch — check exact column names, types, nullability, and keys before writing a SELECT.

-- Postgres
SELECT table_name FROM information_schema.tables WHERE table_schema='public';
SELECT column_name, data_type, is_nullable
FROM information_schema.columns WHERE table_name='orders' ORDER BY ordinal_position;

• SQLite: .tables and PRAGMA table_info(orders);
• MySQL: SHOW TABLES; / DESCRIBE orders;
• DuckDB: SHOW TABLES; / DESCRIBE orders; (or PRAGMA table_info)
• BigQuery: SELECT * FROM dataset.INFORMATION_SCHEMA.COLUMNS WHERE table_name='orders'

Look at a few real rows to learn the data's shape and value conventions: SELECT * FROM orders LIMIT 5;.

2. Explore with guardrails

• Always LIMIT while exploring. Add LIMIT 100 to every ad-hoc query so a fat-fingered join doesn't stream millions of rows. Remove it only for the final aggregate.
• Build joins incrementally: get one table right, add the next, verify row counts don't explode (a fan-out means a wrong/missing join key).
• COUNT(*) and GROUP BY to sanity-check cardinality before selecting detail.
• Prefer explicit column lists over SELECT * in anything you'll keep.

3. Read the plan before running heavy queries

Check the plan before running something that scans or aggregates a lot:

EXPLAIN ANALYZE SELECT ...   -- Postgres/MySQL8/DuckDB: shows real timing + rows
EXPLAIN SELECT ...           -- SQLite/BigQuery dry-run: estimate only

Watch for: Seq Scan / full-table scans on big tables (add or use an index / filter), Nested Loop over large inputs, and estimated-vs-actual row blowups (stale stats). Filter on indexed columns; wrapping a column in a function (WHERE date(ts)=…) usually defeats its index — compare against a range instead (ts >= … AND ts < …).