What is SQL — and Why It Still Wins

TL;DR

SQL is a declarative language for asking questions of structured data. You describe what you want — “the average order total per customer last month, ordered by total descending” — and the database’s query planner figures out how to compute it: which indexes to use, which join algorithm, which order to apply filters. That separation is the entire point. You think in sets and relations; the engine handles the loops.

The relational model underneath SQL is older than most of the people writing it: Edgar Codd published it in 1970. Every “SQL killer” since — object databases in the 90s, key-value stores in the 00s, document stores and “NewSQL” in the 10s — has either died or quietly grown a SQL interface (MongoDB, DynamoDB, Cassandra, even Kafka now). The reason is mundane: declarative + relational + ACID is the right abstraction for the vast majority of data problems, and nobody has come up with a better one.

For ML engineers specifically, SQL is the language your training data is already in. Feature pipelines, label generation, point-in-time joins, backfills, slice analysis, online/offline parity checks — all of it lives in Postgres, Snowflake, BigQuery, or DuckDB before it ever sees a pd.DataFrame. Skip SQL and you spend your career round-tripping data through Python that should have stayed in the warehouse.

In 30 seconds

• Relational model = data lives in tables (relations) with rows (tuples) and columns (attributes).
• SQL = the declarative language for querying and modifying those tables.
• You write what, the planner picks how. That’s the whole abstraction.
• Dialects diverge but the core is shared. Postgres, MySQL, SQLite, Snowflake, BigQuery, DuckDB, ClickHouse all speak ~90% the same SQL.

The vocabulary, once

The relational model, in one breath

A relation is a set of tuples that all share the same attributes. That’s it. Tables are relations. The result of a query is a relation. You can combine relations with operations — selection (filter rows), projection (pick columns), join (combine rows from two relations on a matching condition), union, intersection, difference — and every one of those operations returns another relation. SQL is the syntactic skin on top of that algebra.

The reason this matters: composability. Every query takes relations in and returns a relation. So you can nest queries inside queries, name intermediate results with CTEs, treat views as virtual tables. There’s no “primitive” vs “derived” — users and (SELECT user_id, SUM(total) FROM orders GROUP BY 1) are both just relations the planner can join.

A 30-second example. Two tables:

users                       orders
+----+--------+             +----+---------+--------+--------------+
| id | name   |             | id | user_id | total  | created_at   |
+----+--------+             +----+---------+--------+--------------+
| 1  | Alice  |             | 100| 1       | 19.99  | 2026-04-01   |
| 2  | Bob    |             | 101| 1       | 42.00  | 2026-04-15   |
| 3  | Carol  |             | 102| 2       |  7.50  | 2026-04-20   |
+----+--------+             +----+---------+--------+--------------+

“Total spend per user, including users with zero orders, sorted descending”:

SELECT u.name,
       COALESCE(SUM(o.total), 0) AS spend
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.name
ORDER  BY spend DESC;

Result:

+-------+-------+
| name  | spend |
+-------+-------+
| Alice | 61.99 |
| Bob   |  7.50 |
| Carol |  0.00 |
+-------+-------+

You said what. You did not say “loop over users, for each user scan orders, sum the totals, handle the empty case, sort by spend.” The planner did all of that. On 10 rows it doesn’t matter; on 100M rows it’s the difference between 50ms and your laptop’s fan spinning up.

Why SQL still wins (after every attempted killer)

Five reasons, ordered by how often they pay off.

The non-reason: “it’s elegant”. SQL syntax is a mess of historical accident (why is ORDER BY after WHERE but logically applies to the result of SELECT? Because IBM said so in 1974). Learn it anyway — the abstraction beneath the syntax is what matters.

How a query actually executes

This is the mental model that turns SQL from “magic incantation” into “I know why this is slow”. A query goes through five phases:

1. Parse — text becomes a tree.
2. Bind — table and column names resolve to actual schema objects.
3. Plan — the optimizer picks an algorithm: which index, which join order, which join method (nested loop, hash, merge), whether to parallelize.
4. Execute — the chosen plan runs, reading from tables/indexes, streaming rows through operators.
5. Return — rows ship back to the client, possibly via a cursor.

The planner’s job is the entire game. Two queries that return the same rows can have wildly different plans, and “wildly different” means “milliseconds vs minutes”. EXPLAIN (covered in SQL 302) is how you see the chosen plan.

Logical vs physical execution order. The clauses you write — SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … — are not the order they execute. The planner runs roughly: FROM → WHERE → GROUP BY → HAVING → SELECT (compute expressions) → ORDER BY → LIMIT. That’s why you can’t reference a SELECT alias in WHERE (the alias doesn’t exist yet) but you can in ORDER BY.

SQL’s clauses, by what they do

If you remember nothing else: WHERE filters rows; HAVING filters groups; the rest is detail.

The major SQL dialects, by what they’re for

Same core language, different optimizations and extensions. Pick the one your problem fits.

For ML feature work specifically: Postgres for the app DB, Snowflake or BigQuery for the warehouse, DuckDB for local development and notebooks. ClickHouse if you’re doing real-time event analytics. That covers ~95% of jobs.

A worked example — same question, three engines

The fastest way to feel SQL’s portability: write the same query against three dialects.

Question. “For each user, what fraction of their sessions ended in a purchase, last 30 days?”

sessions(session_id, user_id, started_at, converted boolean)

-- PostgreSQL / Snowflake / DuckDB — all identical
SELECT user_id,
       COUNT(*)                                     AS sessions,
       SUM(CASE WHEN converted THEN 1 ELSE 0 END)   AS conversions,
       AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) AS conv_rate
FROM   sessions
WHERE  started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY user_id
HAVING COUNT(*) >= 5
ORDER  BY conv_rate DESC
LIMIT  100;

The only place this diverges in practice:

• BigQuery writes INTERVAL 30 DAY (no plural, no quotes), uses CURRENT_DATE() (parens), and you’d usually SAFE_DIVIDE instead of relying on integer division.
• MySQL before 8.0 didn’t have WITH or window functions, but for this query the syntax is identical.
• ClickHouse uses today() - 30 and countIf(converted) as a shortcut for the SUM(CASE...) idiom.

The thinking is the same in every dialect. That’s the win.

Why ML engineers need SQL fluency

Three categories of work where being good at SQL pays for itself in a week.

1. Feature engineering happens in the warehouse

Your model takes 200 features. Each one is the answer to a SQL question: “how many orders has this user placed in the last 7/30/90 days?”, “what’s their average session duration in the last 14 days?”, “what fraction of their purchases were in category X?”. Doing that in Python over a Pandas DataFrame works on 10k rows; on 100M rows you OOM your laptop. Doing it in SQL streams through the warehouse engine, runs in parallel, costs cents.

2. Training/serving skew lives in data

The single most common cause of “model works in offline eval, dies in production” is feature drift — the SQL that built the training set used current user state instead of user state at the time of the event. Fix: point-in-time correctness via window functions and AS OF joins (SQL 203, SQL 205). You cannot debug this without reading SQL.

3. Slice analysis and root-causing failures

When the model’s overall AUC is 0.85 but it’s catastrophically wrong on a specific cohort, you find that cohort with GROUP BY and HAVING. When the cohort is 0.1% of the data but 30% of the lawsuits, you find it with a window function. SQL is how you ask “where exactly is my model breaking?”

SQL guides on this site — and where to start

The 24 guides under this topic cover, in rough order:

If you’re starting from zero, the gentlest path is SELECT, WHERE, ORDER BY (SQL 101) → JOINs (SQL 102) → GROUP BY (SQL 103) → NULLs (SQL 109) → Window functions (SQL 201).

If you already know basic SQL and want to level up, jump straight to Window functions (SQL 201), EXPLAIN (SQL 302), and Indexes (SQL 301). Those three lift more engineers out of “I can write SELECT” into “I can ship SQL to production” than any other content on the site.

How to actually learn this

Three rules that compress most SQL advice:

1. Run every query against a real database. SQL is muscle memory. Reading it is not the same as writing it. Spin up Postgres locally (docker run -p 5432:5432 -e POSTGRES_PASSWORD=x postgres:16) or use DuckDB in a notebook. Type the queries out, see the errors, fix them.
2. Always read your own EXPLAIN output. A query that returns the right answer in 50ms on 10k rows can be O(n²) and die at 10M. Every time you write a non-trivial query, run EXPLAIN ANALYZE on it. Two weeks of that habit and you’ll never write an accidentally-quadratic JOIN again.
3. Steal real schemas to practice on. The Pagila DB (github.com/devrimgunduz/pagila) is a Postgres port of the classic Sakila DVD-rental schema, fully populated. The TPC-H benchmark gives you 8 tables of synthetic-but-realistic sales data at any scale. Both beat solving abstract problems.

Common beginner pitfalls

• Treating SQL like a procedural language. “First it does this, then it does this” is wrong — SQL is set-based. Stop thinking row-by-row; start thinking “what relation am I producing”.
• Forgetting NULL is not equal to anything. WHERE x = NULL returns zero rows even when x is NULL. Use IS NULL. See SQL 109.
• SELECT * in production code. It breaks when columns are added or reordered, ships unnecessary bytes over the wire, and on column stores costs you literal money (BigQuery bills per byte scanned).
• Counting on GROUP BY without ORDER BY. Result order is unspecified. If you need rows in an order, say so.
• Joining on a NULL-able column without thinking. NULL = NULL is unknown, so the join silently drops rows. Use IS NOT DISTINCT FROM or coalesce first.
• Optimizing without measuring. “I added an index, it must be faster” isn’t true if the planner ignored it. Run EXPLAIN ANALYZE before and after every change.
• Trusting an ORM to write good SQL. ORMs are great for CRUD; for anything analytical, the SQL they emit is often shockingly bad. Read the SQL your ORM is sending and rewrite the slow ones by hand.

FAQs

Do I need to learn SQL if I use Pandas / Polars / Spark? Yes. Pandas operates on what’s already in memory. SQL is how it gets there. Polars’ LazyFrames and Spark DataFrames are essentially SQL planners with a Python skin — same concepts, same mental model.

Which dialect should I learn first? PostgreSQL. It’s free, ubiquitous, has the best documentation, and its dialect is closest to the ANSI standard. Everything you learn in Postgres transfers to Snowflake/BigQuery/DuckDB with minor syntax tweaks.

Is SQL Turing-complete? Yes, with recursive CTEs (SQL 204) — you can implement Conway’s Game of Life or a Turing machine in pure SQL. You almost never should, but it’s there.

ORMs (SQLAlchemy, Django, Prisma) — should I just use those? For straight-line CRUD, yes. For anything more complex than “fetch by ID and update”, drop to raw SQL. Every senior engineer eventually does. The ORM is the convenient surface; SQL is the actual language of the database.

What about NoSQL? Useful for narrow problems: Redis for caches, DynamoDB for predictable-pattern key-value access, Mongo for genuinely schemaless documents, Elasticsearch for full-text search. None of them replaces a relational warehouse for analytical queries — that’s why every NoSQL store has bolted on a SQL interface.

Should I learn database internals (B-trees, MVCC, write-ahead logs)? Eventually, yes — the Indexes, EXPLAIN, and Transactions guides cover the parts that affect query writing. For deeper reading, Designing Data-Intensive Applications (Kleppmann) is the canonical book.

Is SQL going to be replaced by LLMs that write queries? LLMs are great at the easy 80% of SQL and reliably wrong on the 20% that matters (point-in-time correctness, NULL handling, isolation). Knowing SQL is how you catch when the LLM is confidently wrong — which is daily. The skill just got more important.

Where SQL shows up in real ML systems

• Feature stores (Feast, Tecton, Hopsworks) — under the hood they’re star schemas with point-in-time-correct joins, expressed as SQL or SQL-equivalents over Snowflake/BQ/Spark.
• dbt — models are literally SQL files; dbt builds DAGs of SELECT statements with materializations.
• Airflow / Dagster / Prefect — most production data pipelines are orchestrated SQL: extract from source, run SQL transforms, write back.
• Recommendation systems — candidate generation often runs as a SQL query against a feature warehouse before any ranker touches it.
• Experimentation platforms — A/B test analyses, CUPED variance reduction, segment slicing — all SQL on the events warehouse.
• Model monitoring — drift detection compares production prediction distributions to training distributions; almost always implemented as SQL over the warehouse.
• LLM eval pipelines — store prompt/completion/score triples in Postgres; use SQL to compute rolling pass rates and compare runs.

If you can read and write the SQL these systems generate, you can debug them. If you can’t, every failure becomes “ask the data team.”

Resources

Tutorials and books

• PostgreSQL documentation — postgresql.org/docs — the most thorough free SQL reference; assume PG dialect unless stated otherwise.
• Mode Analytics SQL tutorial — mode.com/sql-tutorial — the cleanest interactive intro on the open web.
• Select Star SQL — selectstarsql.com — free interactive SQL book with a real dataset of executions.
• SQLBolt — sqlbolt.com — short browser-based lessons; good for absolute beginners.
• Use The Index, Luke — use-the-index-luke.com — Markus Winand’s free book on indexing; if you read one thing about SQL performance, read this.
• Designing Data-Intensive Applications — dataintensive.net — Kleppmann’s canonical book on database internals.
• The Data Warehouse Toolkit (Kimball) — the reference for dimensional modeling.

Practice datasets

• Pagila (github.com/devrimgunduz/pagila) — Postgres port of the Sakila DVD-rental schema; populated and immediately useful.
• TPC-H (tpc.org/tpch) — the canonical analytics benchmark; 8 tables, generate at any scale.
• NYC Taxi data (nyc.gov/site/tlc) — billions of rows of real trips; great for window functions and time-series.

Tools

• DuckDB — duckdb.org — local analytics engine; reads CSV/Parquet/Arrow with zero setup.
• PostgreSQL — postgresql.org — the database you should default to.
• dbt — getdbt.com — the standard tool for building modular SQL pipelines.
• explain.depesz.com — explain.depesz.com — paste a Postgres EXPLAIN plan, get a readable analysis.

What’s next

SELECT, WHERE, ORDER BY (SQL 101) is the natural next step if you’re new. If you already know basic SQL, jump to Window Functions (SQL 201) or EXPLAIN and Query Plans (SQL 302) — those are the two guides that turn “writes SQL” into “ships SQL to production”.