**Quick Start:** Build a two-table database with `sqlite3`, then filter, join, group, and rank its rows. The hands-on work takes about 30 minutes, with no server to install. SQL looks like English, which is why it fools people. `SELECT name FROM customers WHERE city = NULL` reads fine and returns nothing. A `LEFT JOIN` with one extra condition in the `WHERE` clause drops the customers you meant to keep, with no error. A report that took two seconds last spring takes two minutes now, and nothing in the query text tells you why. A handful of concepts explain all three failures. ## What You'll Learn * What is SQL, and what problem does it solve? * What are the use cases for SQL, and when is it the wrong tool? * In what order does a database evaluate the clauses of a query? * How do you filter, join, group, and rank rows? * Why does `NULL` break comparisons, and how do you handle it? * How do you read a query plan and make a slow query fast? * Where can you go to learn more about SQL? ## The Basics SQL (Structured Query Language) is the language relational databases speak. You describe the rows you want, and the database decides how to fetch them. You write what you want, and a component called the query planner picks *how*: which indexes to use and in what order to join tables. The same core language runs in SQLite, PostgreSQL, MySQL, SQL Server, DuckDB, and cloud warehouses such as BigQuery, Snowflake, and Amazon Athena. Each engine adds its own functions and quirks, but `SELECT`, `JOIN`, `GROUP BY`, and window functions carry over almost unchanged. This guide covers the 20% of SQL behind 80% of daily work. Execution order, filtering, `NULL` handling, joins, aggregation, common table expressions (CTEs), window functions, safe updates, and query plans are the core. The resources at the end cover the rest, including schema design, normalization, isolation levels, stored procedures, and engine-specific tuning. ### Primary Use Cases * Answer questions about application data: who signed up, what sold, what failed. * Build reports and dashboards from a database or data warehouse. * Transform data in pipelines with tools such as dbt, Spark SQL, or DuckDB. * Read and change data from application code through a driver or an object-relational mapper (ORM). * Debug production issues by inspecting the rows an application wrote. ### Less Suitable Use Cases * Deeply recursive graph traversal, such as friends-of-friends at many hops. `WITH RECURSIVE` handles a few hops, but a graph database fits better as the depth grows. * Free-text search ranking. A search engine such as OpenSearch fits better. * Step-by-step procedural logic with many branches. Application code is easier to test and read. ### When to Use SQL Reach for SQL whenever your data lives in tables and your question is about sets of rows: filter these, match those, count the rest. If you find yourself pulling every row into Python to loop over it, the database can almost always do that work faster and closer to the data. ## The 20% That Does 80% Every example below runs against the same small database, so set it up first. ### Set up a practice database **You'll need:** * `sqlite3`, the SQLite command-line shell. macOS ships it. On Debian or Ubuntu, run `sudo apt install sqlite3`. On Windows, download the command-line tools from the SQLite website, sqlite.org. * A terminal and a text editor. * No prior SQL knowledge. The output below comes from SQLite 3.44. Other versions can word query plans slightly differently, and versions before 3.38 omit the `BLOOM FILTER` line in Step 2. Newer versions, including the `sqlite3` that ships with current macOS, right-align numbers in `.mode column` output; the values match. Check your version with `sqlite3 --version`: window functions need SQLite 3.25 or later. Save this as `shop.sql`: ```sql CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), ordered_on TEXT NOT NULL, total REAL NOT NULL ); INSERT INTO customers (id, name, city) VALUES (1, 'Ada', 'Portland'), (2, 'Grace', 'Seattle'), (3, 'Linus', 'Portland'), (4, 'Edsger', NULL); INSERT INTO orders (id, customer_id, ordered_on, total) VALUES (1, 1, '2026-09-02', 42.50), (2, 1, '2026-09-15', 18.00), (3, 2, '2026-09-20', 99.99), (4, 3, '2026-10-01', 12.25), (5, 1, '2026-10-03', 61.00), (6, 2, '2026-10-07', 7.50); ``` Load it and open the shell: ```bash sqlite3 shop.db < shop.sql sqlite3 shop.db ``` To start over, leave the shell with `.quit`, then delete the database and load it again from your terminal: `rm shop.db && sqlite3 shop.db < shop.sql`. Turn on readable output inside the shell: ```text .headers on .mode column ``` SQLite has no separate date type, so the dates are ISO 8601 text, which sorts correctly as plain strings. Edsger has no city and no orders. Both gaps are on purpose. **What you get:** a `shop.db` file with four customers and six orders, and a prompt that reads `sqlite>`. ### Read a query in execution order You write `SELECT` first, but the database logically evaluates it near the end: `FROM` and `JOIN`, then `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `ORDER BY`, and finally `LIMIT`: ```mermaid flowchart TB A["FROM and JOIN (with ON)"] --> B["WHERE"] B --> C["GROUP BY"] C --> D["HAVING"] D --> E["SELECT (including window functions and DISTINCT)"] E --> F["ORDER BY"] F --> G["LIMIT"] ``` This order explains a whole class of errors. `WHERE` runs before `GROUP BY`, so it can't filter on `SUM(total)`; that's the job of `HAVING`. `SELECT` runs after `WHERE`, so PostgreSQL and most other engines reject a `SELECT` alias inside `WHERE` (SQLite allows it as an extension). The order describes meaning, not the physical steps: engines reorder work freely when the result stays the same. **What you get:** a way to predict what each clause can see before you run the query. ### Filter, sort, and limit ```sql SELECT name, city FROM customers WHERE city = 'Portland' ORDER BY name; ``` ```text name city ----- -------- Ada Portland Linus Portland ``` Name the columns you need instead of writing `SELECT *`. Explicit columns survive schema changes, move less data, and let an index answer the query on its own. Add `ORDER BY` whenever order matters. Without it, rows come back in an unspecified order that depends on the plan. **What you get:** exactly the rows and columns you asked for, in a guaranteed order. ### Treat NULL as unknown `NULL` means "unknown," and comparing anything to unknown yields unknown, which `WHERE` treats as false: ```sql SELECT name FROM customers WHERE city = NULL; -- returns no rows SELECT name FROM customers WHERE city IS NULL; -- returns Edsger ``` Use `IS NULL` and `IS NOT NULL`, and use `COALESCE(column, fallback)` to swap in a default for display or math. **What you get:** filters that find missing values instead of silently skipping them. ### Join tables A join matches rows from two tables on a condition. An inner `JOIN` keeps only matches: ```sql SELECT c.name, o.ordered_on, o.total FROM customers AS c JOIN orders AS o ON o.customer_id = c.id ORDER BY o.ordered_on; ``` ```text name ordered_on total ----- ---------- ----- Ada 2026-09-02 42.5 Ada 2026-09-15 18.0 Grace 2026-09-20 99.99 Linus 2026-10-01 12.25 Ada 2026-10-03 61.0 Grace 2026-10-07 7.5 ``` Edsger disappears because Edsger has no orders. A `LEFT JOIN` keeps every row from the left table and fills the missing side with `NULL`: ```sql SELECT c.name, COUNT(o.id) AS order_count FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY order_count DESC, c.name; ``` ```text name order_count ------ ----------- Ada 3 Grace 2 Linus 1 Edsger 0 ``` `COUNT(o.id)` counts only non-null values, so Edsger gets 0. `COUNT(*)` would count Edsger's row and report 1. Group by `c.id` as well as `c.name`, so two customers who share a name stay separate. **What you get:** answers that include the rows with no match, which is usually what a report needs. ### Put outer-join filters in ON, not WHERE Where you put a condition changes the result of a `LEFT JOIN`. Ask for each customer's October orders with the filter in `WHERE`: ```sql SELECT c.name, o.total FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id WHERE o.ordered_on >= '2026-10-01' ORDER BY c.name; ``` ```text name total ----- ----- Ada 61.0 Grace 7.5 Linus 12.25 ``` Edsger vanished. `WHERE` runs after the join, Edsger's `ordered_on` is `NULL`, and the comparison is unknown, so the row is dropped. The query became an inner join. Move the condition into `ON`: ```sql SELECT c.name, o.total FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id AND o.ordered_on >= '2026-10-01' ORDER BY c.name; ``` ```text name total ------ ----- Ada 61.0 Edsger Grace 7.5 Linus 12.25 ``` For inner joins the two placements return the same rows, and most planners produce the same plan. For outer joins, `ON` decides which rows match and `WHERE` decides which rows survive. **What you get:** outer joins that keep the rows you meant to keep. ### Group and aggregate `GROUP BY` collapses rows into one row per group. Aggregates such as `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX` summarize each group, and `HAVING` filters groups after aggregation: ```sql SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS spent FROM customers AS c JOIN orders AS o ON o.customer_id = c.id GROUP BY c.id, c.name HAVING SUM(o.total) > 50 ORDER BY spent DESC; ``` ```text name orders spent ----- ------ ------ Ada 3 121.5 Grace 2 107.49 ``` Every column in `SELECT` must be either in `GROUP BY` or inside an aggregate. PostgreSQL enforces this with an error, with one exception: a column that depends on a grouped primary key. SQLite lets it slide and picks a value from an arbitrary row. The query runs and returns a wrong answer without an error. **What you get:** totals per group, filtered on the totals themselves. ### Name steps with CTEs and rank with window functions A CTE, written with `WITH`, names an intermediate result so you can read a query top to bottom. A window function computes across related rows without collapsing them the way `GROUP BY` does. Together they answer "latest order per customer": ```sql WITH ranked AS ( SELECT c.name, o.ordered_on, o.total, ROW_NUMBER() OVER ( PARTITION BY c.id ORDER BY o.ordered_on DESC ) AS rn FROM customers AS c JOIN orders AS o ON o.customer_id = c.id ) SELECT name, ordered_on, total FROM ranked WHERE rn = 1 ORDER BY name; ``` ```text name ordered_on total ----- ---------- ----- Ada 2026-10-03 61.0 Grace 2026-10-07 7.5 Linus 2026-10-01 12.25 ``` `PARTITION BY` restarts the numbering for each customer. Drop it and add a running sum to see the other common pattern: ```sql SELECT ordered_on, total, SUM(total) OVER (ORDER BY ordered_on) AS running_total FROM orders ORDER BY ordered_on; ``` ```text ordered_on total running_total ---------- ----- ------------- 2026-09-02 42.5 42.5 2026-09-15 18.0 60.5 2026-09-20 99.99 160.49 2026-10-01 12.25 172.74 2026-10-03 61.0 233.74 2026-10-07 7.5 241.24 ``` **What you get:** "top N per group" and running totals, two questions that are painful without window functions. ### Change data inside a transaction `UPDATE` and `DELETE` without a `WHERE` clause change every row in the table. Run the `WHERE` clause as a `SELECT` first to see exactly which rows you're about to touch, then wrap the change in a transaction, check the result, and decide: ```sql BEGIN; UPDATE orders SET total = total * 0.9 WHERE customer_id = 1; SELECT id, total FROM orders WHERE customer_id = 1; ROLLBACK; ``` ```text id total -- ----- 1 38.25 2 16.2 5 54.9 ``` `ROLLBACK` undoes the discount, and `COMMIT` would keep it. **What you get:** a chance to catch a bad `WHERE` clause before it changes data for good. ### Read the query plan `EXPLAIN` shows how the planner intends to run a query. In SQLite, use `EXPLAIN QUERY PLAN`. In PostgreSQL, use `EXPLAIN`, or `EXPLAIN ANALYZE` to run the query and report real timings. ```sql EXPLAIN QUERY PLAN SELECT id, total FROM orders WHERE customer_id = 1; ``` ```text QUERY PLAN `--SCAN orders ``` `SCAN` reads every row in the table. Add an index on the filtered column and ask again: ```sql CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders (customer_id); EXPLAIN QUERY PLAN SELECT id, total FROM orders WHERE customer_id = 1; ``` ```text QUERY PLAN `--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?) ``` `SEARCH ... USING INDEX` jumps straight to the matching rows. On six rows the difference is invisible. On six million, a scan reads all six million to return a handful. On a busy production table, building an index has a cost of its own. In PostgreSQL, a plain `CREATE INDEX` blocks writes to the table until it finishes; use `CREATE INDEX CONCURRENTLY` there. Wrapping an indexed column in a function hides it from the index. Index the order date, then compare two ways to ask for October: ```sql CREATE INDEX IF NOT EXISTS idx_orders_ordered_on ON orders (ordered_on); EXPLAIN QUERY PLAN SELECT id FROM orders WHERE substr(ordered_on, 1, 7) = '2026-10'; EXPLAIN QUERY PLAN SELECT id FROM orders WHERE ordered_on >= '2026-10-01' AND ordered_on < '2026-11-01'; ``` ```text QUERY PLAN `--SCAN orders USING COVERING INDEX idx_orders_ordered_on QUERY PLAN `--SEARCH orders USING COVERING INDEX idx_orders_ordered_on (ordered_on>? AND ordered_on= '2026-10-01'; ``` ```text QUERY PLAN `--MULTI-INDEX OR |--INDEX 1 | `--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?) `--INDEX 2 `--SEARCH orders USING INDEX idx_orders_ordered_on (ordered_on>?) ``` Drop either index and the plan becomes a `SCAN` of `orders`. * In cloud warehouses, filter on the partition column, usually a date. BigQuery, for example, skips whole partitions when the filter matches the partitioning column. In a warehouse, that pruning often matters more than indexes. **Shrink before joining.** * Filter or aggregate in a CTE first, then join the smaller result. Planners often do this on their own. PostgreSQL 12 and later fold a non-recursive, side-effect-free CTE that the query references once into the parent query, so check the plan to confirm the rewrite changed anything. Measure every change, because a tip that helps one engine can do nothing on another. Run `EXPLAIN` (or `EXPLAIN ANALYZE` in PostgreSQL) before and after each change, and keep the change only when the plan or the timing improves. When the obvious fixes run out, bigger gains often come from reshaping the data, such as adding a summary table, so the query itself gets simpler. ## Troubleshooting ### `no such column: nme` (SQLite) or `column "nme" does not exist` (PostgreSQL) Check the spelling first. In PostgreSQL, the same error appears when `WHERE` uses an alias defined in `SELECT`, because `WHERE` runs before `SELECT` and can't see it. Repeat the expression, or compute it in a CTE and filter in the outer query. SQLite accepts the alias, so a query that works there can fail when you move it to PostgreSQL. ### `ambiguous column name: id` Both joined tables have an `id` column. Prefix it with the table alias: `c.id` or `o.id`. ### `column ... must appear in the GROUP BY clause or be used in an aggregate function` (PostgreSQL) A `SELECT` column is neither grouped nor aggregated, nor does it depend on a grouped primary key. Add it to `GROUP BY` or wrap it in an aggregate. SQLite accepts the same query without complaint and returns a value from an arbitrary row, so fix it there too. ### A `LEFT JOIN` returns fewer rows than the left table A condition on the right-hand table sits in `WHERE`. Move it into `ON`. ### `table customers already exists` or `UNIQUE constraint failed: customers.id` You loaded `shop.sql` into a database that already has the tables. Leave the shell with `.quit`, delete `shop.db`, and load it again: `rm shop.db && sqlite3 shop.db < shop.sql`. ### A syntax error on a query that uses `OVER` Your `sqlite3` predates window functions. Run `sqlite3 --version` and upgrade to 3.25 or later. ### A filter on `= NULL` returns nothing Use `IS NULL`. ### A query got slow as the table grew Run `EXPLAIN QUERY PLAN` (SQLite) or `EXPLAIN ANALYZE` (PostgreSQL). Look for a full `SCAN` on a large table, a missing index on a join column, or a function wrapped around an indexed column. ## Learn SQL: Beyond the Basics You've covered the core. Start with one structured course, practice daily on an interactive site, and keep a performance book nearby for the day a query gets slow. **Video** * [CS50's Introduction to Databases with SQL][cs50-sql]. Harvard's free course: querying, relating, designing, writing, viewing, optimizing, and scaling, taught with SQLite, then PostgreSQL and MySQL. * [SQL Tutorial: Full Database Course for Beginners][fcc-sql]. freeCodeCamp's long-form course on YouTube, good for a weekend of steady practice. **Audio** * [Postgres FM][postgres-fm]. A weekly PostgreSQL podcast hosted by Nikolay Samokhvalov and Michael Christofides. Start with the [EXPLAIN episode][postgres-fm-explain]. **Books** * [Learning SQL, 3rd Edition][learning-sql] by Alan Beaulieu. A steady, engine-neutral introduction for beginners. * [SQL for Data Analysis][sql-for-data-analysis] by Cathy Tanimura. Analytics patterns: time series, cohorts, text, and anomaly detection. * [SQL Antipatterns, Volume 1][sql-antipatterns] by Bill Karwin. Common schema and query mistakes, each with a fix. Read it after your first few months. * [SQL Performance Explained][sql-performance-explained] by Markus Winand. How indexes work across the major engines, written for developers. **Online** * [SQLBolt][sqlbolt]. Short interactive lessons with exercises in the browser. * [Select Star SQL][select-star-sql]. A free interactive book built around one real dataset. * [PostgreSQL Exercises][pgexercises]. Graded practice problems against a sample club database. * [Interview Master][interview-master]. SQL interview practice from Dawn Choo's team. * [DataLemur SQL Tutorial][datalemur]. A free tutorial aimed at data analysts and interview prep. * [Use The Index, Luke][luke]. Markus Winand's free online companion on indexing. * [Modern SQL][modern-sql]. Which standard SQL features each engine supports, including window functions and CTEs. * [Become a SELECT star!][wizard-sql] by Julia Evans. An illustrated zine on how queries behave. ## Related Content * [Fundamentals of Databases][fundamentals-databases], for relational models, keys, and normalization. * [How to Use sqlite-utils][sqlite-utils], for loading data into SQLite from the command line. * [Learn Amazon Athena][learn-athena], for running SQL over files in Amazon S3. * [Fundamentals of Analytics Engineering][fundamentals-analytics-engineering], for testing and layering the SQL you write. [luke]: https://use-the-index-luke.com/ [cs50-sql]: https://cs50.harvard.edu/sql/ [fcc-sql]: https://www.youtube.com/watch?v=HXV3zeQKqGY [postgres-fm]: https://postgres.fm/ [postgres-fm-explain]: https://postgres.fm/episodes/explain [learning-sql]: https://www.oreilly.com/library/view/learning-sql-3rd/9781492057604/ [sql-for-data-analysis]: https://www.oreilly.com/library/view/sql-for-data/9781492088776/ [sql-antipatterns]: https://pragprog.com/titles/bksap1/sql-antipatterns-volume-1/ [sql-performance-explained]: https://sql-performance-explained.com/ [sqlbolt]: https://sqlbolt.com/ [select-star-sql]: https://selectstarsql.com/ [pgexercises]: https://pgexercises.com/ [interview-master]: https://www.interviewmaster.ai/ [datalemur]: https://datalemur.com/sql-tutorial [modern-sql]: https://modern-sql.com/ [wizard-sql]: https://wizardzines.com/zines/sql/ [fundamentals-databases]: https://jeffbailey.us/blog/2025/09/24/fundamentals-of-databases/ [sqlite-utils]: https://jeffbailey.us/blog/2022/05/12/how-do-i-use-sqlite-utils/ [learn-athena]: https://jeffbailey.us/blog/2020/06/06/learn-amazon-athena/ [fundamentals-analytics-engineering]: https://jeffbailey.us/blog/2026/07/09/fundamentals-of-analytics-engineering/