SQL Language Fundamentals

Table of Contents#

1. Why an SRE Needs to Read SQL, Even Without Writing Much of It
2. The Relational Model, in Plain English
3. SELECT — Reading Data
4. Filtering, Sorting, and Limiting
5. INSERT, UPDATE, DELETE — Changing Data
6. JOINs — Combining Data From Multiple Tables
7. The Four JOIN Types, Visually
8. Aggregation — GROUP BY and Aggregate Functions
9. Subqueries and CTEs
10. Normalization — Why Tables Are Split Up At All
11. Indexes — Why Some Queries Are Fast and Others Are Slow
12. Reading an EXPLAIN Plan
13. Transactions in Practice
14. Common SQL Mistakes That Cause Real Production Incidents
15. Common Mistakes
16. Worked Practice Problems
17. Summary and What's Next

---

Why an SRE Needs to Read SQL, Even Without Writing Much of It#

Parts 1-3 of this series covered how relational databases replicate, shard, and recover — all of it assumed familiarity with the language actually used to interact with the data itself: SQL (Structured Query Language). An SRE rarely writes complex application queries, but reading and reasoning about SQL is a genuinely everyday skill — diagnosing a slow query during an incident, reviewing a risky migration before it ships, or understanding why a specific query is locking a table all require real SQL fluency, not just conceptual database knowledge.

---

The Relational Model, in Plain English#

A relational database organizes data into tables (rows and columns), where each table represents one kind of "thing," and relationships between things are expressed by referencing one table's row from another.

Diagram

Simple analogy: think of a table as a spreadsheet tab — users is one tab, orders is another. Each row is one specific record (one specific user, one specific order). A foreign key (orders.user_id) is simply a column in one tab that points to a specific row's ID in another tab — exactly like a cell that says "see row 42 on the Users tab" instead of copying that user's entire information into every single order row.

---

SELECT — Reading Data#

The single most common SQL statement — retrieving data from a table.

Copy
-- Get every column, every row (rarely a good idea on a big table)
SELECT * FROM users;

-- Get specific columns only
SELECT id, name, email FROM users;

A genuinely important, practical habit worth stating explicitly: avoid SELECT * in real application code and especially in production migrations. It retrieves every column whether needed or not (wasted bandwidth/memory), and — more dangerously — if someone later adds a new column to the table, SELECT *-based code can silently start behaving differently without any code change at all, since it's now pulling back data it never explicitly asked for.

---

Filtering, Sorting, and Limiting#

Copy
-- WHERE filters which ROWS are returned
SELECT name, email FROM users WHERE created_at > '2026-01-01';

-- ORDER BY controls the RESULT order
SELECT name FROM users ORDER BY created_at DESC;

-- LIMIT caps how many rows come back
SELECT name FROM users ORDER BY created_at DESC LIMIT 10;

Diagram

A genuinely important, real-world operational habit worth stating explicitly, tying directly to production safety: always use LIMIT when exploring data in a production database, and always double- and triple-check a WHERE clause exists before running any UPDATE or DELETE — this connects directly to the DevSecOps series' pipeline-safety discussion and the "unscoped DELETE" disaster scenario from Part 3 of this very series.

---

INSERT, UPDATE, DELETE — Changing Data#

Copy
-- Add a new row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Modify existing rows — ALWAYS scope with WHERE
UPDATE users SET email = 'newemail@example.com' WHERE id = 42;

-- Remove rows — ALWAYS scope with WHERE
DELETE FROM users WHERE id = 42;

Diagram

A concrete, practical safety habit worth citing explicitly: before running any real UPDATE/DELETE in production, first run the equivalent SELECT with the exact same WHERE clause to confirm precisely which rows would actually be affected — catching a wrong or missing filter before it does real, permanent damage, rather than after.

---

JOINs — Combining Data From Multiple Tables#

A JOIN combines rows from two (or more) tables based on a related column — this is the entire point of splitting data into separate tables in the first place (covered fully in the Normalization section below).

Copy
SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id;

In plain English: "for every order, look up the matching user (using the user_id reference) and show me both the user's name and that order's total, side by side."

---

The Four JOIN Types, Visually#

This is one of the single most commonly asked SQL interview topics — genuinely worth being able to draw and explain each one from memory.

Diagram

A worked, concrete example showing exactly why the choice matters, worth being able to reproduce: "Show me every user, and how many orders they've placed, including users who've never ordered anything."

Copy
-- INNER JOIN would be WRONG here — it silently EXCLUDES
-- users with zero orders, undercounting your actual user base
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.name;
-- Users with NO orders simply don't appear in the results at all!

-- LEFT JOIN is CORRECT here — every user appears, with
-- order_count = 0 for anyone who's never ordered
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.name;

Why this specific example is such a strong interview answer: it demonstrates that choosing the wrong JOIN type doesn't cause an error — it causes a silently, subtly WRONG result (undercounting the user base by excluding non-ordering users entirely), which is exactly the kind of bug that's hard to catch in casual testing and can quietly corrupt business-critical reports or dashboards for a long time before anyone notices.

---

Aggregation — GROUP BY and Aggregate Functions#

Copy
-- Count orders PER user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Common aggregate functions
SELECT
  COUNT(*) AS total_orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value,
  MAX(total) AS biggest_order,
  MIN(total) AS smallest_order
FROM orders;

A genuinely common, important gotcha worth naming explicitly: WHERE filters rows BEFORE grouping; HAVING filters groups AFTER aggregation — you cannot use an aggregate function like COUNT(*) inside a WHERE clause, because WHERE runs before the aggregation even happens.

Copy
-- Find users with MORE THAN 5 orders — needs HAVING, not WHERE,
-- because it's filtering on an AGGREGATED value
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

---

Subqueries and CTEs#

A subquery is a query nested inside another query; a CTE (Common Table Expression) is a named, reusable subquery defined up front with WITH, usually far more readable for anything non-trivial.

Copy
-- Subquery: find users who have placed at least one order over $1000
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- The SAME logic, as a CTE — genuinely easier to read for
-- anything with multiple steps
WITH big_spenders AS (
  SELECT user_id FROM orders WHERE total > 1000
)
SELECT name FROM users WHERE id IN (SELECT user_id FROM big_spenders);

Why CTEs are worth knowing specifically, beyond just "another way to write a subquery": they let a complex query be broken into clearly-named, logical steps, read top to bottom, exactly like naming intermediate variables in application code instead of writing one giant, deeply nested expression — genuinely valuable both for humans reading the query later and for a database's own query planner, which can sometimes optimize a CTE more effectively than an equivalent deeply-nested subquery.

---

Normalization — Why Tables Are Split Up At All#

This directly connects to why JOINs are even necessary — worth understanding the underlying design principle, not just the mechanics of writing queries.

Diagram

The formal levels worth knowing by name, at a conceptual level (deep, rigorous coverage isn't necessary for most SRE interviews, but recognizing the names and the core idea is): 1NF (each column holds a single, atomic value — no lists crammed into one field), 2NF (every non-key column depends on the whole primary key, not just part of it), 3NF (every non-key column depends only on the primary key, not on another non-key column) — each level is about progressively eliminating a specific category of data duplication and the inconsistency risk that comes with it.

A genuinely important, balanced point worth stating explicitly: normalization isn't free — it means more JOINs to reconstruct a full picture, which costs real query performance. This is precisely why denormalization (deliberately duplicating some data back in, for read performance) is a real, common, deliberate tradeoff in high-read-volume systems — directly connecting to the caching discussion from the Capacity Planning & Performance series: sometimes the fastest "join" is simply not needing to do one at all, because the data was already duplicated where it's read.

---

Indexes — Why Some Queries Are Fast and Others Are Slow#

Already touched on conceptually elsewhere in this course — here's the concrete, SQL-level mechanics.

Diagram

Copy
-- Create an index on a frequently-queried column
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- A composite (multi-column) index — order of columns matters!
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

Why indexes aren't a free "add it everywhere and everything gets faster" win, worth stating explicitly, and a genuinely common interview follow-up: every index has to be updated on every INSERT/UPDATE/DELETE to the table, meaning more indexes make writes slower, and every index also consumes real disk space. The real, practical skill is choosing indexes deliberately, based on the actual, real query patterns the application runs most often — not indexing every column defensively.

Why column order in a composite index matters, a genuinely sharp, commonly-tested detail: an index on (user_id, created_at) efficiently supports queries filtering by user_id alone, or by user_id AND created_at together — but it does NOT efficiently support a query filtering by created_at alone, since the index is fundamentally structured (sorted) by user_id first. This is exactly analogous to a phone book sorted by last name, then first name — you can efficiently find "all the Smiths" or "John Smith specifically," but you can't efficiently find "everyone named John" without scanning the whole book, since the book isn't sorted by first name at the top level.

---

Reading an EXPLAIN Plan#

EXPLAIN shows exactly how the database intends to actually execute a given query — the single most important diagnostic tool for understanding why a specific query is slow.

Copy
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18584.00 rows=1 width=64)
  Filter: (user_id = 42)

Copy
-- After adding the index from earlier:
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=64)
  Index Cond: (user_id = 42)

Why the difference between "Seq Scan" and "Index Scan" is such a genuinely important, concrete thing to recognize, worth stating explicitly: Seq Scan (sequential/full table scan) means the database is checking every single row — exactly the slow path an index exists to avoid. Index Scan means it's using the sorted index structure to jump directly to matching rows. Seeing an unexpected Seq Scan on a large table, where an Index Scan was expected, is one of the single most common, most actionable findings when diagnosing a slow production query during a real incident.

---

Transactions in Practice#

Directly connecting to the ACID discussion from Part 3 of this series — here's the concrete SQL syntax.

Copy
BEGIN;

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

COMMIT;
-- If anything went wrong instead: ROLLBACK;

Diagram

Why wrapping both updates in a single transaction is essential here, directly reusing Atomicity from Part 3's ACID discussion: without a transaction, if the SECOND update failed after the FIRST one already succeeded, money would simply vanish — debited from account 1 but never credited to account 2. The transaction guarantees these two statements succeed or fail as one indivisible unit, exactly the Atomicity guarantee ACID promises.

---

Common SQL Mistakes That Cause Real Production Incidents#

A concrete, memorable list worth having ready — this section deliberately mirrors the "common mistakes" pattern from every other tutorial in this course, but focused specifically on genuinely common, real SQL-authoring incidents.

Diagram

A worked example of the "missing JOIN condition" disaster, worth being able to explain concretely:

Copy
-- MISSING the ON clause entirely (a genuine, real typo risk)
SELECT users.name, orders.total
FROM users, orders;
-- Produces a CARTESIAN PRODUCT: EVERY user paired with EVERY
-- order, regardless of whether they actually belong together.
-- 1,000 users × 10,000 orders = 10,000,000 nonsense result rows!

Why this is dangerous beyond just "returns wrong data": on a large enough table, a cartesian product can generate an enormous, unexpected result set that consumes massive memory/disk, potentially degrading or crashing the database entirely — a small, easy-to-make syntax mistake with an outsized, genuinely severe operational impact.

---

Common Mistakes#

---

Worked Practice Problems#

Problem 1: A report is supposed to show every product and its total sales, including products that have never sold. The current query uses INNER JOIN between products and order_items, and management notices the report is missing several known products. What's wrong, and how do you fix it?

Answer: INNER JOIN only returns rows that match in BOTH tables — a product with zero rows in order_items (never sold) has nothing to match, so it's silently excluded from the results entirely, exactly explaining the missing products. The fix: use LEFT JOIN from products to order_items instead, so every product appears regardless of whether it has any matching sales, with SUM(order_items.total) naturally coming out as 0 (or NULL, depending on how it's aggregated) for products with no sales.

Problem 2: A query filtering WHERE created_at = '2026-06-01' on a 50-million-row table is taking 30 seconds, and EXPLAIN shows Seq Scan. There's already an index on (user_id, created_at). Why isn't the index helping, and what would you recommend?

Answer: The existing composite index is structured (sorted) by user_id FIRST, then created_at — it can efficiently support queries filtering by user_id alone or by user_id AND created_at together, but it can't efficiently support a query filtering by created_at alone, since the index isn't sorted by that column at the top level (exactly like a phone book sorted by last name can't efficiently answer "everyone with a given first name"). The fix, assuming this specific access pattern (filtering purely by date) is common enough to justify it: create a separate, dedicated index specifically on created_at alone (or reconsider whether this query pattern is common enough to warrant one, weighed against the write-performance cost of an additional index).

Problem 3: During an incident, a query is found to be running for over 10 minutes, and other queries against the same table are timing out. Investigation shows the slow query is a multi-table SELECT using old-style comma-separated FROM users, orders, products syntax with a WHERE clause that appears incomplete. What's the likely root cause, and why is it also causing OTHER queries to fail?

Answer: This has the hallmark signature of a cartesian product — the incomplete WHERE clause likely isn't actually joining the tables correctly (missing the equivalent of an ON condition matching foreign keys), causing every row in one table to be paired with every row in the others, producing a massive, unintended result set. This explains both the extreme runtime (processing millions of nonsense combined rows instead of the intended, much smaller matched set) and why other queries are timing out — the runaway query is likely consuming enormous memory/disk I/O and potentially holding locks on the involved tables for its entire, abnormally long execution, starving other legitimate queries of the same resources.

---

Summary and What's Next#

• SQL organizes data into tables, with relationships expressed via foreign keys referencing another table's rows — JOINs reconstruct the full picture across related tables.
• The four JOIN types — INNER (only matches), LEFT (all of the left table, matched or not), RIGHT (mirror of LEFT), FULL OUTER (all of both) — pick the wrong one and you get a silently, subtly WRONG result, not an error.
• WHERE filters rows before aggregation; HAVING filters groups after aggregation — you can't use an aggregate function inside WHERE.
• Normalization eliminates data duplication and inconsistency risk by storing each fact in exactly one place — at the real cost of needing more JOINs, which is exactly why deliberate denormalization is a legitimate, common performance tradeoff.
• Indexes turn a slow, full table scan into a fast, targeted index scan — but every index costs write performance and storage, so they should be added deliberately, based on real query patterns, and column order in a composite index genuinely matters.
• EXPLAIN is the single most important diagnostic tool for understanding why a query is slow — spotting an unexpected Seq Scan on a large table is one of the most common, actionable findings during a real production slow-query incident.
• Transactions (BEGIN/COMMIT/ROLLBACK) provide the Atomicity guarantee from ACID (Part 3) — multiple statements succeed or fail together, as one indivisible unit.
• Real, common SQL-authoring mistakes — an unscoped DELETE, a missing JOIN condition producing a cartesian product, an unindexed column on a huge table — are genuine, recurring causes of real production incidents, not just academic concerns.

Continue to Part 5 (05-nosql-database-types.md) to see how NoSQL databases deliberately trade away some of the relational model's structure (and SQL itself) for different scalability and flexibility tradeoffs.