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. To try the queries first, use the Practice SQL section, which runs them in your browser.

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.

Every query in this guide runs in sqlite3, the same shell as the Practice SQL section. SELECT, JOIN, GROUP BY, and window functions look similar in other engines, but the setup, plans, errors, and indexes below are SQLite’s.

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 schema design, normalization, and moving the same ideas onto a server database later.

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.
  • A terminal and a text editor.
  • No prior SQL knowledge.

macOS ships sqlite3. On Windows, download the command-line tools from the SQLite website, sqlite.org. On Debian or Ubuntu, install it from your terminal:

Runs on your machine, not in the practice shell

sudo apt install sqlite3

The output below comes from SQLite 3.54.1; other versions can word query plans slightly differently, and versions before 3.38 omit the BLOOM FILTER line in Step 2. Older versions left-align numbers in .mode column output; the values match. Window functions need SQLite 3.25 or later, so check your version from your terminal:

Runs on your machine, not in the practice shell

sqlite3 --version

Save this as shop.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:

Runs on your machine, not in the practice shell

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:

Runs on your machine, not in the practice shell

rm shop.db && sqlite3 shop.db < shop.sql

Turn on readable output inside the shell:

.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:

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 an alias defined in SELECT is not part of the standard language in WHERE. SQLite accepts that alias as an extension. Write the expression twice, or name it in a CTE and filter in the outer query, so the same SQL still means the same thing when you leave sqlite3. The order describes meaning, not the physical steps: SQLite reorders 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

SELECT name, city
FROM customers
WHERE city = 'Portland'
ORDER BY name;
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:

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:

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;
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:

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;
 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:

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;
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:

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;
 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:

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;
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. SQLite does not enforce that. Leave c.city out of GROUP BY and out of an aggregate, and SQLite still runs the query and fills city from an arbitrary row in the group. Group by c.id and every column you display, or wrap the extra column in MAX or MIN, so the result does not depend on which row SQLite picked.

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”:

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;
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:

SELECT ordered_on, total,
       SUM(total) OVER (ORDER BY ordered_on) AS running_total
FROM orders
ORDER BY ordered_on;
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:

BEGIN;
UPDATE orders SET total = total * 0.9 WHERE customer_id = 1;
SELECT id, total FROM orders WHERE customer_id = 1;
ROLLBACK;
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 QUERY PLAN prints how SQLite intends to run a query. EXPLAIN without QUERY PLAN dumps the virtual-machine opcodes; skip that listing until you need it. The plan is a prediction, not a stopwatch. In desktop sqlite3, turn on .timer on after you have a plan you like, then run the query.

EXPLAIN QUERY PLAN
SELECT id, total FROM orders WHERE customer_id = 1;
QUERY PLAN
`--SCAN orders

SCAN reads every row in the table. Add an index on the filtered column and ask again:

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;
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.

CREATE INDEX writes into the database file and holds a lock while it runs. On a file other connections also have open, build the index during a quiet window, or copy the file, index the copy, and swap it in.

Wrapping an indexed column in a function hides it from the index. Index the order date, then compare two ways to ask for October:

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';
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<?)

COVERING INDEX holds every column the query needs, so SQLite never reads the table itself. It does not narrow the search: SCAN still reads every index entry, and only SEARCH jumps to the matching ones.

The first query scans because the index stores ordered_on, not substr(ordered_on, 1, 7). The range version searches.

What you get: a way to tell whether the database searches or scans, before a slow query reaches production.

Practice SQL

Try every query in this article without installing anything. The practice below runs SQLite 3.54.1’s own shell in your browser, with shop.db already loaded and .headers on and .mode column set, so the output looks like the blocks above. Press Start, type or paste a query into the editor, and press Run. Choose Open full screen to give it the whole window.

The exercise list holds ten tasks drawn from the core sections, from filtering and sorting to reading a query plan and rolling back a change. Choose an exercise to put its starting query in the editor, change the query until it does the task, and press Check. Check runs your query and the task’s answer on fresh copies of the practice data and compares the results, not the SQL text, so a query written differently from the answer still passes when it gets the same result. Hint and Show answer teach in place when you get stuck, so Open full screen and the standalone page stay useful without jumping back here. Reset brings the practice data back.

Every example also works in your own sqlite3. The page has no terminal, no files, and no other programs, so a few things run only on your machine:

  • Terminal lines. sqlite3 shop.db < shop.sql and rm shop.db give SQLite’s parse error.
  • Program and leave commands. .shell, .system, .cd, .quit, and .exit are not in this build.
  • File-writing commands. .once, .output, .excel, and .www are not in this build, so the next query prints on the page.
  • File-reading commands. .read, .import, .backup, .save, .restore, .archive, .clone, and .load are not in this build.
  • Safe-mode command. .nonce is not in this build either; it matters only for desktop sqlite3 started with --safe.

Practice the commands on this page in real SQLite, right here. Nothing downloads until Start (about 1.4 MB).

Each exercise gives you a starting query. It runs and shows the data the task is about, but it does not do the task yet: change it until it does. Hint and Show answer stay on this page when you get stuck.

Filter, sort, and limit
  1  List the two largest orders, the biggest first

Treat NULL as unknown
  2  List the customers with no city on file

Join tables
  3  Show each order's id with the name of the customer who placed it
  4  List the customers who have never ordered

Put outer-join filters in ON, not WHERE
  5  List every customer with the number of their orders over 50, zero included

Group and aggregate
  6  List the customers with two or more orders, with their order count and total spent, highest spend first

Name steps with CTEs and rank with window functions
  7  Rank each customer's orders from the largest to the smallest
  8  Name the customers who spent more than the average customer

Change data inside a transaction
  9  Raise every Portland order by 10%, look at the change, then undo it

Read the query plan
 10  Make the lookup of one customer's orders use an index

The SQLite practice needs JavaScript. Every command in the reference also works in SQLite on your own machine.

Build Something: A Customer Spending Report

Combine the pieces into one report: every customer, their order count, total spend, last order date, and spend rank, including customers with no orders.

Step 1: Write the report query

Aggregate per customer in a CTE, then rank in the outer query. The LEFT JOIN keeps Edsger, COUNT(o.id) counts Edsger’s orders as zero, and COALESCE turns a NULL spend into 0.

WITH per_customer AS (
  SELECT c.id, c.name,
         COUNT(o.id) AS orders,
         COALESCE(SUM(o.total), 0) AS spent,
         MAX(o.ordered_on) AS last_order
  FROM customers AS c
  LEFT JOIN orders AS o ON o.customer_id = c.id
  GROUP BY c.id, c.name
)
SELECT name, orders, spent, last_order,
       RANK() OVER (ORDER BY spent DESC) AS spend_rank
FROM per_customer
ORDER BY spend_rank;

Expected output:

 name   orders  spent   last_order  spend_rank
------  ------  ------  ----------  ----------
Ada          3   121.5  2026-10-03           1
Grace        2  107.49  2026-10-07           2
Linus        1   12.25  2026-10-01           3
Edsger       0       0                       4

What you learned: GROUP BY runs first and collapses orders into one row per customer; the window function then ranks those rows without collapsing them further.

Step 2: Check the join in the plan

Make sure the index from the previous section exists. The statement is safe to run twice:

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);

Ask for the plan of the join at the heart of the report:

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

Expected output:

QUERY PLAN
|--SCAN c
`--SEARCH o USING INDEX idx_orders_customer_id (customer_id=?) LEFT-JOIN

Drop the index and ask again:

DROP INDEX idx_orders_customer_id;

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
QUERY PLAN
|--SCAN c
|--BLOOM FILTER ON o (customer_id=?)
`--SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN

AUTOMATIC COVERING INDEX builds a temporary index for this one query and throws it away afterward. A Bloom filter is a probabilistic check SQLite uses to avoid unnecessary row lookups during the join. It has no impact on correctness.

Both lines appear because the permanent index on orders.customer_id is gone. Recreate it:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

What you learned: index the columns you join on, and treat an automatic index in a plan as a request for a permanent one.

Write Queries That Stay Fast

Fast queries come from three habits: skip work the result doesn’t need, let indexes do the searching, and shrink tables before you join them.

Skip unneeded work.

  • Sort once, in the outermost query. Only the outermost ORDER BY guarantees the order of the result, so an ORDER BY inside a CTE or subquery adds a sort the final result can’t rely on.
  • Use UNION ALL when duplicates can’t occur or don’t matter. UNION removes duplicate rows, which costs a sort or hash step; UNION ALL keeps every row and skips it.
  • Write DISTINCT only when duplicates are possible. Reaching for it to hide extra rows usually signals a join that matches more rows than you expected; fix the join instead.

Let indexes search.

  • Filter on indexed columns, and compare the bare column, as the ordered_on range above shows.

  • SQLite can use indexes for an OR across different columns only when each branch has an index of its own. Otherwise it scans the whole table, so check the plan before and after a rewrite. With both indexes from the previous section in place, the October-or-customer query searches twice:

    EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE customer_id = 1 OR ordered_on >= '2026-10-01';
    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.

  • After a large load, run ANALYZE so SQLite records row counts. Without those stats, the planner guesses, and a plan that was cheap on six rows can stay a SCAN after the table grows.

Shrink before joining.

  • Filter or aggregate in a CTE first, then join the smaller result. SQLite may materialize that CTE into a temporary table, or fold it into the parent query. Prefix the CTE with MATERIALIZED or NOT MATERIALIZED when you need one or the other, then confirm with EXPLAIN QUERY PLAN.

Run EXPLAIN QUERY PLAN before and after each change, and keep the change only when the plan gets cheaper: a SEARCH in place of a SCAN, or a covering index in place of a table read. 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

Parse error near line 1: no such column: nme.

Check the spelling first. If the name is an alias from SELECT used in WHERE, SQLite may still accept it. Repeat the expression, or compute it in a CTE and filter in the outer query, so you are not relying on that extension.

ambiguous column name: id.

Both joined tables have an id column. Prefix it with the table alias: c.id or o.id.

A grouped query returns a city, or a name, you did not expect

A SELECT column is neither grouped nor aggregated. SQLite still runs the query and fills that column from an arbitrary row in the group. Add the column to GROUP BY, or wrap it in MAX or MIN.

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. 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

Audio

  • The Untold Story of SQLite. Adam Gordon Bell interviews D. Richard Hipp on CoRecursive: how SQLite started, and why a single-file database ships in so many products.

Books

Online

Related Content