SQL

Who This Is For

Anyone who works with data or builds applications and needs to understand databases. No prior SQL knowledge required. This guide starts from scratch but moves quickly.

By the end, you'll understand how databases think about data (which is different from how most people think), be able to write and read queries with confidence, understand why some queries are slow and others fast, and know exactly where AI-generated SQL tends to go wrong.

Why SQL Is Worth Learning Properly

SQL has been around since the 1970s and isn't going anywhere. It's the language of virtually every relational database — PostgreSQL, MySQL, SQLite, SQL Server, and more.

You might wonder: if AI can write SQL for me, why do I need to learn it? Two reasons.

First, AI-generated SQL is often subtly wrong in ways that aren't immediately obvious. It handles happy paths well but misses edge cases, generates queries that work but are dangerously slow at scale, or produces logic that's almost right but wrong in the wrong situations. You need enough fluency to review what it generates.

Second, SQL fluency changes how you think about data. Understanding how databases work gives you intuitions about performance, data structure, and system design that are useful far beyond writing queries.

Tables Are Sets of Rows

A relational database stores data in tables. A table is like a spreadsheet: it has columns that define what's stored, and rows where the actual data lives.

But unlike a spreadsheet, a table has no inherent order. There's no "row 1" and "row 2" in any meaningful sense — rows don't have positions. When you query a database without specifying order, you get rows back in arbitrary order. If you need a specific order, you have to ask for it explicitly.

Each Table Represents One Kind of Thing

Good database design follows a principle: each table represents one type of entity. A users table represents users. An orders table represents orders. A products table represents products.

When you want to connect entities — a user has many orders, an order contains many products — you use relationships, not by stuffing more columns into the same table.

SQL Is Declarative

Most programming languages are imperative — you tell the computer what steps to follow. SQL is declarative — you describe what result you want and the database engine figures out how to get it.

-- You're not saying "scan row by row and check each one"
-- You're saying "give me this shape of data"
SELECT name, email
FROM users
WHERE created_at > '2024-01-01';

This is why thinking about SQL procedurally ("first get all users, then filter them") leads you astray. Think about the shape of the data you want, not the steps to get there.

The Result Is Also a Table

Every query returns a result set, which is itself a table — rows and columns. This means queries can be composed: you can use the result of one query as the input to another. That's what subqueries and CTEs do.

SELECT, FROM, WHERE

The foundational building block:

SELECT column1, column2
FROM table_name
WHERE condition;

SELECT — What Columns to Return

SELECT * returns all columns. SELECT name, email returns only those two. In practice, prefer naming columns explicitly — SELECT * is convenient but creates fragile queries and returns data you might not need.

-- Transform values inline
SELECT
  name,
  UPPER(email) AS email_upper,
  price * quantity AS total
FROM orders;

AS renames a column in the result (called an alias). The original table isn't changed.

WHERE — Filter Rows

WHERE narrows the result to rows that match a condition. Without it, you get all rows.

SELECT name FROM users WHERE active = true;
SELECT name FROM users WHERE age >= 18;
SELECT name FROM users WHERE country = 'US';

Combine conditions with AND and OR:

SELECT name FROM users WHERE active = true AND country = 'US';
SELECT name FROM users WHERE plan = 'pro' OR plan = 'enterprise';

Execution Order (Not Write Order)

The order you write a query is not the order the database executes it:

1. FROM — identify the source table
2. WHERE — filter rows
3. SELECT — pick which columns to return

This matters: you can't use a SELECT alias in a WHERE clause, because WHERE runs before SELECT.

NULL — The Special Case That Breaks Everything

NULL represents the absence of a value. It's not zero, not an empty string, not false — it's nothing. And NULL has special comparison rules that trip everyone up.

You cannot compare NULL with =:

-- WRONG: never matches anything
SELECT * FROM users WHERE last_login = NULL;

-- RIGHT: use IS NULL / IS NOT NULL
SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM users WHERE last_login IS NOT NULL;

NULL also propagates: any arithmetic or comparison involving NULL returns NULL. 5 + NULL = NULL. NULL = NULL is NULL (not true). This is counterintuitive but consistent: "unknown" plus anything is still "unknown."

ORDER BY and LIMIT

ORDER BY — Sort the Result

Default is ascending (smallest to largest, A to Z):

SELECT name, created_at FROM users ORDER BY created_at;
SELECT name, created_at FROM users ORDER BY created_at DESC;  -- newest first

Sort by multiple columns — primary sort first, secondary as tiebreaker:

SELECT name, country, created_at FROM users
ORDER BY country, created_at DESC;

LIMIT and OFFSET — Pagination

SELECT name FROM users ORDER BY name LIMIT 20;              -- first page
SELECT name FROM users ORDER BY name LIMIT 20 OFFSET 20;   -- second page
SELECT name FROM users ORDER BY name LIMIT 20 OFFSET 40;   -- third page

Aggregate Functions

Aggregation turns many rows into summary values.

SELECT COUNT(*) FROM users;                       -- total users
SELECT COUNT(*) FROM users WHERE active = true;   -- active users
SELECT COUNT(last_login) FROM users;              -- non-NULL values only

SELECT SUM(amount) FROM orders;        -- total revenue
SELECT AVG(amount) FROM orders;        -- average order value
SELECT MIN(created_at) FROM users;     -- earliest signup
SELECT MAX(amount) FROM orders;        -- largest order

GROUP BY and HAVING

Without GROUP BY, aggregate functions collapse all rows into one summary. With it, they compute the summary separately for each group.

-- Total revenue by country
SELECT country, SUM(amount) AS total_revenue
FROM orders
GROUP BY country;

-- Order count per user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

HAVING — Filter Groups

WHERE filters rows before grouping. HAVING filters groups after aggregation.

-- Countries with more than 1000 users
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 1000;

-- Users with more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

You can't use WHERE for this because the aggregated value doesn't exist until after GROUP BY runs.

Full Execution Order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Combining Tables with Joins

A join takes two tables and produces a new table by matching rows from each based on a condition. The most common: a key in one table matches a key in another.

SELECT users.name, orders.amount, orders.created_at
FROM orders
JOIN users ON orders.user_id = users.id;

This returns one row for each order, with the user's name attached.

INNER JOIN (the Default)

JOIN and INNER JOIN are the same thing. Returns only rows where the join condition matches in both tables. Orders without a matching user are excluded. Users without any orders are excluded.

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right. Where there's no match, right-table columns are NULL.

-- All users, including those with zero orders
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

RIGHT JOIN and FULL JOIN

RIGHT JOIN is the mirror of LEFT — all rows from the right table. In practice, most people use LEFT JOIN and swap the table order instead.

FULL JOIN returns all rows from both tables, with NULLs where there's no match. Useful for finding unmatched rows on either side.

Joining More Than Two Tables

SELECT users.name, products.name, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON order_items.product_id = products.id;

The Most Common Join Mistakes

Forgetting INNER JOIN Excludes Non-Matching Rows

If you expect to see all users but use INNER JOIN with orders, users who've never ordered disappear. Use LEFT JOIN when you need all rows from one side.

Cartesian Products from Missing Join Conditions

If you JOIN two tables without an ON clause (or with an always-true condition), you get every combination of rows. A table with 1,000 rows joined to another with 1,000 rows produces 1,000,000 rows.

Duplicate Rows from One-to-Many Joins

If a user has 5 orders, a join returns that user's info 5 times. When counting or summing, this inflates your results. The fix is usually to aggregate the many side first (in a subquery or CTE), then join.

Building Queries in Stages

Real queries often require building results in stages. Two tools for this: subqueries and CTEs.

Subqueries

A subquery is a query nested inside another query. The inner query runs first, and its result is used by the outer query.

-- Users whose orders are above average
SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE amount > (SELECT AVG(amount) FROM orders)
);

Subqueries in FROM clauses create a derived table:

-- Average orders per user, across all users
SELECT AVG(order_count) AS avg_orders_per_user
FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) AS user_order_counts;

CTEs — Common Table Expressions

CTEs (introduced with WITH) are named subqueries that appear before the main query. Functionally similar to derived tables, but vastly more readable for complex queries:

WITH user_order_counts AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
),
high_value_users AS (
  SELECT user_id
  FROM user_order_counts
  WHERE order_count > 10
)
SELECT users.name, user_order_counts.order_count
FROM users
JOIN user_order_counts ON users.id = user_order_counts.user_id
WHERE users.id IN (SELECT user_id FROM high_value_users);

Each CTE is defined once and can be referenced multiple times. This makes complex queries dramatically more readable and maintainable.

When to Use Which

Use a subquery for simple, one-off filtering when the nested query is short. Use a CTE when the logic is complex, when you need to reference the same result multiple times, or when readability matters. For most real-world complex queries, CTEs win.

INSERT, UPDATE, DELETE

INSERT — Adding Rows

-- Insert one row
INSERT INTO users (name, email, created_at)
VALUES ('Alice Johnson', '[email protected]', NOW());

-- Insert multiple rows
INSERT INTO products (name, price, category)
VALUES
  ('Widget A', 9.99, 'widgets'),
  ('Widget B', 14.99, 'widgets'),
  ('Gadget C', 49.99, 'gadgets');

UPDATE — Changing Existing Rows

UPDATE users SET last_login = NOW() WHERE id = 42;

UPDATE products SET price = price * 1.10 WHERE category = 'widgets';

DELETE — Removing Rows

DELETE FROM sessions WHERE expires_at < NOW();
DELETE FROM users WHERE id = 42;

Same warning: DELETE FROM users without WHERE deletes every row.

Transactions — All or Nothing

A transaction groups multiple statements so they either all succeed or all fail together:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong between BEGIN and COMMIT, you can ROLLBACK to undo everything. This is critical for operations that need to stay consistent.

Schema Design Basics

A schema is the structure of your database — which tables exist, what columns they have, how they relate. Good schema design makes correct data easy to store and incorrect data hard.

Normalization — The Core Intuition

Each piece of information should exist in exactly one place. If you store a user's email in the users table and in every row of the orders table, what happens when the email changes? You have to update it in many places — and if you miss one, your data is inconsistent.

Instead: store the email once in users, and reference it by user ID from orders.

Primary Keys and Foreign Keys

Every table should have a primary key — a column that uniquely identifies each row. A foreign key refers to the primary key of another table:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  amount DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT NOW()
);

REFERENCES users(id) tells the database that user_id must contain a value that exists in users.id. The database enforces this.

Choosing Data Types

Constraints

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  last_login TIMESTAMP  -- nullable: new users haven't logged in
);

NOT NULL rejects inserts that omit the value. DEFAULT provides a fallback. UNIQUE ensures no duplicates. These constraints enforce data integrity at the database level, which is more reliable than application-level checks alone.

Indexes — The Speed Layer

An index is a data structure that makes certain queries faster. Without an index, finding all users where email = '[email protected]' requires reading every row. With an index, the database jumps directly to matching rows.

When the Database Uses an Index

Indexes help when you're filtering (WHERE email = ...), sorting (ORDER BY email), or joining (ON users.id = orders.user_id) on the indexed column.

-- Index on created_at WILL be used:
WHERE created_at > '2024-01-01'

-- Index on created_at will NOT be used (column inside a function):
WHERE DATE(created_at) > '2024-01-01'

The Cost of Indexes

Indexes make reads faster but writes slower — every INSERT, UPDATE, or DELETE must also update all indexes. Don't index everything. Index columns you frequently filter, join, or sort on.

EXPLAIN — How to Know If You Need an Index

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Look for "Seq Scan" on large tables — that's a full table scan, often meaning a missing index. "Index Scan" means it's using an index. The output is dense, but the key signal is whether it's scanning the whole table or not.

Performance Pitfalls

The N+1 Problem

The most common performance bug in application code that uses a database.

It happens when you run one query to get N rows, then run another query for each row — N+1 queries total instead of one or two.

# BAD: 101 queries
users = db.query("SELECT * FROM users LIMIT 100")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = " + user.id)

The fix: fetch everything in one or two queries using JOINs or WHERE IN (for more on N+1 and other database performance patterns, see the Performance field guide):

-- GOOD: 1 query
SELECT orders.*
FROM orders
WHERE user_id IN (SELECT id FROM users LIMIT 100);

SELECT * in Production Code

SELECT * is convenient for exploration, but in production it returns unused columns (waste), breaks if columns change (fragility), and prevents certain optimizations. Name your columns explicitly.

OFFSET Pagination at Scale

LIMIT 20 OFFSET 10000 is slow for large offsets because the database still processes all skipped rows. For large datasets, use cursor-based pagination:

-- Cursor pagination (fast at any depth)
WHERE id > last_seen_id ORDER BY id LIMIT 20

Comparing the Wrong Types

Comparing a string column to a number (or vice versa) can prevent indexes from being used and produce unexpected results. WHERE user_id = '42' instead of WHERE user_id = 42 can be surprisingly problematic.

Ignoring NULLs in Aggregation

COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. AVG(column) ignores NULLs (doesn't divide by NULL rows). Understanding which aggregate functions ignore NULLs prevents subtle miscounts.

AI-Generated SQL — What to Watch For

AI tools generate SQL quickly and often get it right. They also fail in specific, predictable ways. Knowing these failure modes makes you an effective reviewer.

What AI Does Well

Standard patterns: basic SELECTs, simple JOINs, GROUP BY aggregations, straightforward CRUD. Also useful for explaining unfamiliar SQL, suggesting indexes, translating between SQL dialects, and reformatting messy queries.

Where AI-Generated SQL Goes Wrong

Wrong join type. AI often defaults to INNER JOIN when you need LEFT JOIN. "All users with their order count" using INNER JOIN excludes users with no orders.

Duplicate rows from one-to-many joins. A query for "total revenue per user" that joins orders and order_items without proper aggregation may double-count.

Counting NULLs incorrectly. COUNT(*) where COUNT(column) is needed causes the "user with no orders gets count 1" bug.

Missing WHERE on UPDATE/DELETE. If your prompt is ambiguous, AI might omit WHERE. Always verify write operations.

Not matching your schema. AI guesses column names and data types unless you tell it. Verify against your actual tables.

Functions that break indexes. WHERE DATE(created_at) > ... instead of range conditions on the raw column.

SQL dialect mismatches. PostgreSQL, MySQL, and SQLite differ in syntax. Common: date functions, string concatenation (|| vs CONCAT), auto-increment syntax, JSON operators.

How to Use AI for SQL Effectively

• Give it your schema. Paste the relevant CREATE TABLE statements. AI generates dramatically better SQL with concrete schema information.
• Tell it your database. "I'm using PostgreSQL" changes what syntax it uses.
• Ask it to explain. "Explain this query and any edge cases" often surfaces NULL and join issues.
• Ask about scale. "This table has 50 million rows — any performance concerns?" prompts index consideration.
• Review writes with extra care. The cost of a wrong SELECT is a wrong answer. The cost of a wrong DELETE is potentially unrecoverable data loss.

Quick Reference

Query Structure (Execution Order)

SELECT columns         -- (5) pick columns
FROM table             -- (1) identify source
JOIN other_table       -- (2) combine tables
  ON join_condition
WHERE row_condition    -- (3) filter rows
GROUP BY columns       -- (4) aggregate
HAVING group_condition -- (5) filter groups
ORDER BY columns       -- (6) sort
LIMIT n OFFSET m;      -- (7) paginate

Filtering

WHERE col = 'value'             WHERE col IS NULL
WHERE col != 'value'            WHERE col IS NOT NULL
WHERE col > 100                 WHERE cond1 AND cond2
WHERE col BETWEEN 10 AND 100    WHERE cond1 OR cond2
WHERE col IN ('a', 'b', 'c')   WHERE col LIKE '%pattern%'

Joins

Aggregate Functions

AI-Generated SQL Checklist

What You Can Do Now

The best way to learn SQL is to run queries against real data.

If you have a database you work with, open a read-only connection and start exploring. If you don't, SQLite is free, requires no setup, and runs in memory — you can have a database and sample data running in a few minutes.

Start simple: SELECT * FROM table LIMIT 10 to see what's in a table. Add a WHERE clause. Count rows. GROUP BY something. Join two tables.

The mental model — sets, relationships, shapes of data — will click faster than you expect once queries start running.