SQL

What is SQL?

SQL, pronounced “sequel” or “S-Q-L”, is the Structured Query Language used to read and write data in relational databases. It was standardized in 1986, which makes it older than the web, and it is still the way nearly every application talks to its data.

SQL has two halves. DDL (data definition language) creates and changes structure: CREATE TABLE, ALTER TABLE, DROP INDEX. DML (data manipulation language) works with the data itself: SELECT, INSERT, UPDATE, DELETE. Most of your time is spent in DML, with SELECT by far the most common statement.

The important thing to understand early is that SQL is not a programming language in the usual sense. There are no loops in the core language and no variables in a plain query. You describe a result, and the database figures out how to produce it.

Declarative and set-based

In a language like JavaScript or Python you would compute a report by iterating:

const result = [];
for (const author of authors) {
  let count = 0;
  for (const post of posts) {
    if (post.author_id === author.id) count++;
  }
  if (count > 0) result.push({ author, count });
}

In SQL you state the same intent in one expression:

SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0;

There is no loop. You declared that you want each author with their post count. The database may satisfy that with an index scan, a hash join or something else entirely — and if you add an index tomorrow, the same query can get faster without changing a character.

This is the set-based mindset: statements operate on whole sets of rows at once. Once it clicks, SQL becomes concise in a way that loops rarely are.

SELECT: choosing columns

Every read starts with SELECT, which lists the columns you want.

SELECT id, title, published_at
FROM posts;

SELECT * returns every column and is fine while exploring, but name your columns in application queries. Explicit lists are stable when the schema changes, avoid transferring large unused columns, and make the intent obvious. You can compute new columns and rename them:

SELECT title,
       LENGTH(body) AS body_length,
       COALESCE(published_at, created_at) AS visible_at
FROM posts;

AS gives a column an alias. Use it to make results readable and to give computed columns a name, which matters when a client library maps rows into objects.

Filtering with WHERE

WHERE keeps only the rows that satisfy a condition. It supports the usual comparisons plus AND, OR, NOT, IN, BETWEEN and LIKE.

SELECT id, title
FROM posts
WHERE author_id = 7
  AND published_at IS NOT NULL
  AND title ILIKE '%sql%';

Two details are worth committing to memory. First, LIKE is case-sensitive on many databases while ILIKE (Postgres) is case-insensitive; MySQL’s LIKE is case-insensitive with the usual collations. Second, a leading wildcard such as '%sql' prevents an ordinary index from helping, because there is no prefix to seek to. For real text search, use a full-text index instead.

Sorting and paging

ORDER BY sorts the result, and LIMIT with OFFSET slices it.

SELECT id, title
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

Without ORDER BY, the database makes no promise about row order. A query that “happens” to come back sorted today can change when the planner picks a different plan, so always sort explicitly when order matters.

OFFSET skips rows, which becomes expensive deep into a large table because the database still walks past them. Keyset pagination avoids the cost by remembering the last row seen:

SELECT id, title
FROM posts
WHERE published_at < :last_published_at
ORDER BY published_at DESC
LIMIT 20;

NULL and three-valued logic

NULL does not mean zero or an empty string. It means unknown, and SQL uses three-valued logic: every condition evaluates to true, false or unknown. Rows only pass a WHERE clause when the condition is true, so unknown is treated like false for filtering.

SELECT id FROM posts WHERE published_at = NULL;     -- never matches
SELECT id FROM posts WHERE published_at IS NULL;    -- correct
SELECT id FROM posts WHERE published_at IS NOT NULL;

Consequences to watch for:

• NULL = NULL is unknown, not true. Use IS NULL or IS NOT DISTINCT FROM.
• NULL + 1 is NULL; use COALESCE(x, 0) to substitute a default.
• NOT IN (1, 2, NULL) is never true, because comparing with the unknown element yields unknown. Prefer NOT EXISTS when the list may contain NULL.
• Aggregates ignore NULL: COUNT(column) counts non-null values, while COUNT(*) counts rows.

Nullability is a design decision. Declare columns NOT NULL unless missing values are genuinely meaningful.

JOINs, with a diagram

A join combines rows from two tables based on a related column. Suppose authors has Ada, Grace and Linus, and posts has two rows by Ada and one by Grace. The join types produce different results.

authors            posts
-------            ----------------------------
id name            id author_id title
1  Ada             10 1         Joins
2  Grace           11 1         Indexes
3  Linus           12 2         NULLs

INNER JOIN  ->  posts 10, 11, 12        (only matches)
LEFT JOIN   ->  posts 10, 11, 12, Linus (all authors, NULL post)
RIGHT JOIN  ->  posts 10, 11, 12        (all posts)
FULL JOIN   ->  posts 10, 11, 12, Linus (both sides)

-- Every author, including those with no posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
ORDER BY a.name;

The join condition lives in ON. For outer joins, adding a filter on the right table to WHERE silently turns the outer join into an inner one, because NULL rows fail the filter. Put such conditions in the ON clause instead when you want unmatched rows to survive.

-- Keeps authors with no published posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p
  ON p.author_id = a.id
 AND p.published_at IS NOT NULL;

A self join uses the same table twice with different aliases, useful for hierarchies. A cross join pairs every row with every row and is rarely what you want by accident.

GROUP BY and HAVING

Aggregates collapse many rows into one: COUNT, SUM, AVG, MIN, MAX. GROUP BY defines the groups.

SELECT a.name          AS author,
       COUNT(p.id)     AS post_count,
       MAX(p.created_at) AS latest
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC;

The rule is that every selected column must either be in the GROUP BY or wrapped in an aggregate. Databases that enforce this (Postgres, and MySQL 8 by default) are protecting you from arbitrary results.

WHERE filters rows before grouping, and HAVING filters groups after. A condition on an aggregate therefore belongs in HAVING, and a condition on a plain column usually belongs in WHERE for efficiency.

SELECT author_id, COUNT(*) AS posts
FROM posts
WHERE published_at IS NOT NULL   -- filter rows
GROUP BY author_id
HAVING COUNT(*) > 5;             -- filter groups

Subqueries and CTEs

A subquery is a query nested inside another. It can appear in SELECT, FROM or WHERE.

SELECT title
FROM posts
WHERE author_id IN (
  SELECT id FROM authors WHERE name = 'Ada'
);

A common table expression (CTE) names a subquery with WITH, which usually reads better than nesting and lets you reuse the result.

WITH published AS (
  SELECT id, author_id, title
  FROM posts
  WHERE published_at IS NOT NULL
)
SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN published p ON p.author_id = a.id
GROUP BY a.id, a.name;

CTEs can be chained, and a recursive CTE references itself to walk a tree such as categories or comment threads. Reach for a CTE whenever a query starts to nest more than one level deep; readability is a performance feature too.

Writing rows

INSERT adds rows, UPDATE changes them and DELETE removes them.

INSERT INTO posts (author_id, title, body)
VALUES (7, 'Learning SQL', 'SQL is declarative.');

UPDATE posts
SET published_at = now()
WHERE id = 42;

DELETE FROM posts
WHERE id = 42;

Always give UPDATE and DELETE a WHERE clause. Without one, every row is affected. A good habit is to run the matching SELECT first and confirm the count. An UPDATE can also reference existing values:

UPDATE posts
SET title = title || ' (updated)'
WHERE author_id = 7;

INSERT can add several rows at once and can be fed from a query:

INSERT INTO posts (author_id, title)
SELECT id, 'Welcome' FROM authors;

Transactions

A transaction groups statements so they all succeed or all fail. This is what keeps a multi-step change consistent.

BEGIN;

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

COMMIT;

If a statement fails, or you decide to abort, ROLLBACK undoes every change since BEGIN. Without a transaction, a crash between the two updates would leave money missing. Wrap related writes together, keep the transaction short, and never wait on a user or a network call while holding one open.

Keys and constraints

Constraints are rules the database enforces for you:

• PRIMARY KEY — uniquely identifies each row; also creates an index and implies NOT NULL.
• FOREIGN KEY — requires the value to exist in another table, preserving referential integrity.
• UNIQUE — forbids duplicates in a column or combination of columns.
• NOT NULL — requires a value.
• CHECK — requires a condition to hold, such as price >= 0.
• DEFAULT — supplies a value when none is given.

CREATE TABLE posts (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    bigint NOT NULL REFERENCES authors (id) ON DELETE CASCADE,
  title        text NOT NULL,
  body         text NOT NULL,
  published_at timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now()
);

A surrogate primary key such as an identity column is stable and compact. A natural key such as an email address is meaningful but changes, so prefer a surrogate key with a UNIQUE constraint on the natural one.

Indexes at a conceptual level

An index is a separate, sorted structure that maps column values to row locations. Without one, finding rows means scanning the table; with one, the database can jump straight to the matches.

CREATE INDEX posts_author_published_idx
  ON posts (author_id, published_at DESC);

Conceptually, think of a phone book sorted by last name. Looking up one name is fast because the book is ordered; filtering on a column that is not the sort key means reading every page. Indexes trade write speed and storage for read speed, because every insert and update must maintain them.

Index the columns you filter and join on, prefer composite indexes that match real query shapes, and check with EXPLAIN whether the planner actually uses them. More indexes is not better; the right indexes are.

Window functions in one sitting

A window function computes across a set of rows related to the current row, without collapsing them the way GROUP BY does. This makes running totals, ranks and per-group comparisons easy.

SELECT author_id,
       title,
       published_at,
       ROW_NUMBER() OVER (
         PARTITION BY author_id
         ORDER BY published_at DESC
       ) AS rank_in_author
FROM posts
WHERE published_at IS NOT NULL;

The OVER clause defines the window: PARTITION BY splits rows into groups, and ORDER BY orders them inside each group. Common functions include ROW_NUMBER, RANK, LAG, LEAD and SUM(...) OVER (...). The key difference from aggregation is that every original row survives.

Normalization: 1NF, 2NF, 3NF

Normalization is the process of removing redundancy so each fact is stored once. The first three normal forms are the ones you will use:

First normal form (1NF) — no repeating groups or multi-value columns. A tags column holding "sql,indexes" violates it; a separate post_tags table fixes it.

Second normal form (2NF) — 1NF plus no partial dependency on part of a composite key. If an order line is keyed by (order_id, product_id) and stores product_name, that name depends only on product_id, so it belongs in products.

Third normal form (3NF) — 2NF plus no transitive dependency between non-key columns. If posts stored both author_id and author_email, the email would depend on the author, not the post, so it belongs in authors.

Consider a table that repeats the author’s email on every post. Change the email and you must update many rows; miss one and the data contradicts itself. Split it into authors and posts and the fact lives once. Denormalize later, deliberately, when a measured performance problem justifies it.

Best practices

• Name columns explicitly instead of using SELECT * in application queries.
• Always ORDER BY when the order of results matters.
• Declare NOT NULL by default and handle missing values with COALESCE.
• Use IS NULL rather than = NULL, and prefer NOT EXISTS over NOT IN with nullable lists.
• Filter rows in WHERE and groups in HAVING.
• Use CTEs to keep complex queries readable.
• Give UPDATE and DELETE a WHERE clause and confirm the target rows first.
• Wrap related writes in a transaction and keep it short.
• Add indexes for real query patterns and verify them with EXPLAIN.
• Store each fact once and reach for third normal form before denormalizing.

Common mistakes

• Writing WHERE column = NULL and getting zero rows.
• Using NOT IN against a list that contains NULL and silently returning nothing.
• Forgetting WHERE on an UPDATE or DELETE and modifying every row.
• Selecting non-grouped columns and getting arbitrary values.
• Assuming rows come back in a useful order without ORDER BY.
• Using LIMIT ... OFFSET for deep pagination and paying a growing cost.
• Filtering the right table in WHERE and accidentally turning a LEFT JOIN into an inner join.
• Indexing every column and slowing down writes for no read benefit.
• Concatenating user input into SQL strings instead of using parameters, which invites injection.
• Storing repeated facts in one wide table instead of normalizing them.

Where to go next

SQL is the foundation under every relational database, so the next step is choosing an engine and learning its dialect and quirks. Start with PostgreSQL for its standards compliance and rich types, or MySQL for its ubiquity and replication story. From there, see how query results become HTTP responses in the REST guide, and how a Node.js service sends those queries with a pooled connection in Node.js basics.