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:

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:

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:

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

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

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

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.

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:

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.

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.

  • 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

Audio

Books

Online

Related Content