paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

SQL Fundamentals: A Hands-On Guide to the Basics Every Developer Needs

From SELECT statements to joins and transactions - a practical, no-fluff introduction to relational databases for engineers who want to actually understand what's happening under the hood

Introduction

Every application that stores data eventually has a conversation with a database, and for the vast majority of systems in production today, that conversation happens in SQL. Structured Query Language has been around since the 1970s, standardized by ANSI and ISO, and yet it remains one of the most consistently useful skills a software engineer can have. Frameworks come and go, ORMs rise and fall in popularity, but the relational model and the language used to interact with it have proven remarkably durable.

This durability is not an accident. SQL is declarative: you describe what data you want, not how to retrieve it. That separation between intent and execution is what allows a database engine to apply decades of research in query optimization, indexing strategies, and concurrency control without the developer needing to reimplement any of it. Understanding SQL well means understanding not just the syntax, but the mental model of sets, relations, and constraints that underpins it. This article is written for engineers who already know how to code but want a solid, practical foundation in SQL - the kind that lets you write correct, performant queries and reason clearly about what a database is actually doing when you ask it something.

Context: Why Relational Databases Still Matter

It would be easy to assume that in a world of document stores, key-value caches, and graph databases, the relational model is legacy technology. The opposite is true. Relational databases such as PostgreSQL, MySQL, SQL Server, and Oracle remain the default choice for systems that need strong consistency guarantees, well-defined schemas, and the ability to express complex relationships between entities. The reason is structural: most business data is inherently relational. Customers place orders, orders contain line items, line items reference products - these are relationships, and SQL was designed from the ground up to model and query relationships efficiently.

The relational model itself was introduced by Edgar F. Codd in his 1970 paper "A Relational Model of Data for Large Shared Data Banks," which proposed representing data as sets of tuples grouped into relations (tables), with operations defined by relational algebra. SQL, developed shortly afterward at IBM, became the practical language for interacting with that model, and was later standardized by ANSI in 1986 and ISO in 1987. That standard has evolved through several revisions - SQL-92, SQL:1999, SQL:2003, SQL:2011, and more recent updates - but the core grammar has stayed remarkably stable, which is why SQL knowledge transfers so well across different database engines.

Beyond historical inertia, there's a practical reason relational databases persist: ACID guarantees. Atomicity, Consistency, Isolation, and Durability give engineers a predictable foundation for building systems where correctness matters - financial transactions, inventory counts, user account state. NoSQL systems often trade some of these guarantees for horizontal scalability or flexible schemas, which is the right trade-off for certain workloads, but it is a trade-off, not a free upgrade. Knowing SQL well means knowing when that trade-off is worth making, and when it isn't.

Core Concepts: Tables, Rows, and the Relational Model

At its foundation, a relational database organizes data into tables, where each table represents an entity type (users, orders, products) and each row represents a single instance of that entity. Columns define the attributes of that entity, and each column has a defined data type - integer, text, timestamp, boolean, and so on. This might sound elementary, but the discipline of enforcing types and structure at the storage layer is precisely what gives relational databases their reliability advantage over more loosely structured alternatives.

Two ideas sit at the center of everything else in SQL: primary keys and foreign keys. A primary key uniquely identifies each row in a table - commonly an auto-incrementing integer or a UUID - and the database enforces that this value is unique and non-null. A foreign key is a column (or set of columns) in one table that references the primary key of another table, and this is the mechanism through which relationships are expressed. When you see an orders table with a customer_id column referencing customers.id, you are looking at a foreign key relationship, and the database can be configured to enforce referential integrity - refusing to let you insert an order for a customer that doesn't exist, for example.

Normalization is the discipline of structuring these tables to minimize redundancy and avoid update anomalies. The formal normal forms - first, second, and third normal form, among others - were also introduced by Codd, and while few production schemas are purists about every rule, understanding the reasoning behind normalization helps you recognize when a schema is going to cause maintenance headaches. A denormalized schema that stores a customer's name directly on every order row, for instance, means that a customer name change requires updating every historical order - a clear sign that the data should live in one place and be referenced, not duplicated.

Deep Technical Explanation: How a Query Actually Executes

When you write a SQL query, you are not telling the database the steps to take - you're describing the result set you want, and the query planner decides how to get there. This is worth internalizing because it changes how you think about performance. Two queries that return identical results can have wildly different execution costs depending on indexes, table statistics, and the query planner's chosen strategy (a full table scan versus an index seek, for example).

Most relational engines follow a similar conceptual pipeline: parsing the SQL into an abstract syntax tree, validating it against the schema, generating one or more candidate execution plans, estimating the cost of each plan using statistics about table size and data distribution, and finally executing the cheapest plan found. This is why running EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) in front of a query is one of the most valuable habits a developer can build - it shows you exactly what the engine intends to do, rather than leaving you to guess.

Indexes are the single most impactful performance lever available to you as a SQL user. A B-tree index, the default in most systems, allows the engine to locate rows matching a condition in logarithmic time rather than scanning every row linearly. But indexes are not free: they consume disk space and slow down writes, since every insert or update must also update the index structure. The trade-off is straightforward in principle - index columns that are frequently filtered, joined, or sorted on - but in practice it requires understanding your actual query patterns, not guessing at what "seems important."

Transactions and isolation levels are the other pillar of the deep technical picture. A transaction groups multiple statements into a single atomic unit: either all of them succeed and are committed, or none of them take effect. Isolation levels - read uncommitted, read committed, repeatable read, and serializable, as defined in the SQL standard - control how much one transaction can see of another transaction's in-progress changes. Choosing an isolation level is a trade-off between consistency guarantees and concurrency throughput, and different engines implement the same nominal isolation level with different underlying mechanisms (PostgreSQL's MVCC versus lock-based approaches in other systems), so it pays to read your specific engine's documentation rather than assuming behavior is identical everywhere.

Practical Examples: Writing Real Queries

Theory is useful, but SQL is best learned by writing it. Consider a simple e-commerce schema with three tables: customers, orders, and order_items. Here is how you would create them, expressing the relationships discussed earlier directly in the DDL (Data Definition Language):

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    placed_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_name VARCHAR(200) NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0)
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Notice the CHECK constraint on quantity and the NOT NULL constraints throughout - these are the database enforcing data integrity rules so that application code doesn't have to be the last line of defense. The indexes on foreign key columns are added explicitly because, contrary to a common assumption, most databases (PostgreSQL included) do not automatically index foreign key columns; you have to do it yourself if you'll be joining or filtering on them frequently.

Now consider a realistic reporting query: finding the total revenue per customer for orders placed in the last 30 days, including only customers who have at least one completed order.

SELECT
    c.id AS customer_id,
    c.full_name,
    SUM(oi.unit_price * oi.quantity) AS total_revenue,
    COUNT(DISTINCT o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
    AND o.placed_at >= now() - INTERVAL '30 days'
GROUP BY c.id, c.full_name
HAVING SUM(oi.unit_price * oi.quantity) > 0
ORDER BY total_revenue DESC
LIMIT 20;

This single query demonstrates several core SQL mechanics working together: joins to traverse relationships, aggregation with SUM and COUNT, filtering with WHERE (applied before grouping) versus HAVING (applied after grouping), and ORDER BY combined with LIMIT to return a bounded, ranked result set. Understanding the logical order in which a database processes these clauses - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT - is essential, because it differs from the order in which you write them, and misunderstanding this is a common source of bugs, such as trying to reference a SELECT alias inside a WHERE clause.

For application code, most engineers won't write raw SQL strings by hand in every layer - they'll use a query builder or ORM. But understanding the underlying SQL matters even then, because ORMs can generate inefficient queries if you don't understand what they're doing. Here's a TypeScript example using a lightweight query builder pattern (illustrative of libraries like Knex.js) that mirrors the SQL above:

interface CustomerRevenue {
  customerId: number;
  fullName: string;
  totalRevenue: number;
  orderCount: number;
}

async function getTopCustomersLast30Days(
  db: Knex,
  limit = 20
): Promise<CustomerRevenue[]> {
  const results = await db("customers as c")
    .join("orders as o", "o.customer_id", "c.id")
    .join("order_items as oi", "oi.order_id", "o.id")
    .where("o.status", "completed")
    .andWhere("o.placed_at", ">=", db.raw("now() - interval '30 days'"))
    .groupBy("c.id", "c.full_name")
    .havingRaw("SUM(oi.unit_price * oi.quantity) > 0")
    .select(
      "c.id as customerId",
      "c.full_name as fullName",
      db.raw("SUM(oi.unit_price * oi.quantity) as totalRevenue"),
      db.raw("COUNT(DISTINCT o.id) as orderCount")
    )
    .orderBy("totalRevenue", "desc")
    .limit(limit);

  return results;
}

The value of writing it this way, rather than hand-rolling string concatenation, is parameterization: the query builder ensures that any dynamic values are passed as bound parameters rather than interpolated directly into the SQL string, which is your primary defense against SQL injection. This matters regardless of whether you're using an ORM, a query builder, or raw SQL with a driver library - parameterized queries should be the default, not an afterthought.

Trade-offs and Common Pitfalls

The N+1 query problem is probably the most common performance pitfall for developers coming from an application-code background rather than a database background. It happens when code fetches a list of parent records and then, in a loop, issues a separate query for each parent's related child records - resulting in one query plus N additional queries instead of a single join. ORMs make this mistake easy to write accidentally because lazy loading hides the extra queries behind what looks like simple object property access. The fix is almost always to express the relationship as a single join or to use the ORM's eager-loading feature explicitly, and the way you catch it in the first place is by watching your actual query logs in a staging environment, not by assuming your ORM is smart enough to avoid it.

Over-indexing is a subtler trade-off. It's tempting to add an index for every column that ever appears in a WHERE clause, but every additional index adds overhead to every INSERT, UPDATE, and DELETE on that table, and it consumes memory that could otherwise be used for caching frequently accessed data pages. The right approach is to index based on observed query patterns - using tools like PostgreSQL's pg_stat_statements or slow query logs - rather than speculative indexing based on what "might" be queried someday.

Another common mistake is misunderstanding NULL semantics. In SQL's three-valued logic, NULL represents an unknown value, and comparisons against NULL using standard operators return NULL rather than true or false. This means WHERE column = NULL will never match any row, even rows where the column genuinely is null - you must use IS NULL instead. This trips up even experienced developers, particularly when writing dynamic query conditions that build up WHERE clauses programmatically and don't account for the null case explicitly.

Finally, there's the trade-off between normalization and query complexity. A fully normalized schema minimizes redundancy but can require many joins to answer a single business question, which adds both query complexity and execution cost. Many production systems deliberately denormalize specific tables or maintain materialized views for reporting purposes, accepting some redundancy in exchange for query simplicity and speed. This isn't a failure of database design - it's a conscious trade-off that should be documented and revisited as the system's read and write patterns evolve.

Best Practices for Writing Production-Grade SQL

Write explicit column lists instead of SELECT *. This seems like a minor style preference, but it has real consequences: SELECT * couples your application code to the exact current shape of the table, meaning a schema change (like adding a new column) can silently change the shape of data your application receives, and it also prevents the database from applying certain optimizations that come from knowing exactly which columns are needed.

Always use parameterized queries or prepared statements, never string concatenation, when building queries with user-supplied input. This is not a "best practice" in the aspirational sense - it is a hard requirement for avoiding SQL injection, one of the longest-standing and most damaging classes of vulnerability in software, as documented extensively by OWASP. Even internal tools and admin panels should follow this rule, since assumptions about "trusted" input have a long history of turning out to be wrong.

Design transactions to be as short as possible. A long-running transaction holds locks (or, in MVCC systems, prevents old row versions from being cleaned up) for longer than necessary, which can create contention and degrade performance for concurrent operations. If you need to do expensive external work - calling an API, for instance - do that work outside the transaction boundary, and only wrap the actual database writes in the transaction itself.

Use migrations for schema changes, and treat them with the same rigor as application code changes. Tools like Flyway, Liquibase, or framework-native migration systems (Django migrations, Rails' ActiveRecord migrations, Prisma Migrate) let you version-control your schema alongside your application code, review changes before they're applied, and roll back safely if something goes wrong. Manually running ALTER TABLE statements against production without a tracked migration history is a practice that tends to catch up with teams eventually, usually at an inconvenient time.

Key Takeaways

Mental Model: SQL as Set Algebra, Not a To-Do List

One of the biggest shifts for developers coming from imperative programming languages is realizing that SQL is not a sequence of instructions - it's a description of a set operation. Thinking of a JOIN as "for each row in table A, look at rows in table B" is a useful mental starting point, but the more accurate model is that a join produces a new set formed by combining rows from two relations wherever a specified condition holds true. The database is free to compute that set in whatever order is most efficient, including in parallel across multiple rows at once, precisely because you never told it a sequence of steps - you told it a condition.

A useful analogy is the difference between giving someone driving directions versus giving them a destination address and a working GPS. Imperative code is like driving directions: step one, then step two, then step three, in that exact order. SQL is like handing over a destination and letting a well-tuned GPS system - the query planner - figure out the fastest route based on live conditions it can observe (indexes, statistics, current load) that you, the person writing the query, don't have visibility into at write time. This is precisely why two structurally different queries that return the same result set can have wildly different performance profiles, and why the discipline of checking execution plans matters more than intuition about how you'd solve the problem procedurally.

Conclusion

SQL rewards depth of understanding in a way that few technologies do, precisely because its core ideas - relations, sets, constraints, transactions - have remained stable for decades while still applying directly to systems built today. The skills covered here: modeling relationships with primary and foreign keys, writing joins and aggregations correctly, understanding how indexes and query plans affect performance, and using transactions safely, form the foundation that every more advanced topic (window functions, common table expressions, query optimization at scale, replication strategies) builds on top of.

The most effective way to solidify this knowledge is to practice against a real database rather than only reading about it. Spin up a local PostgreSQL or MySQL instance, load in a realistic dataset, and deliberately try to break your own assumptions - write a query you expect to be slow and check EXPLAIN ANALYZE to see if you're right, or write a schema and try to violate its constraints on purpose to see how the database responds. That hands-on friction is where SQL fundamentals actually become intuition, and it's the difference between knowing the syntax and knowing how to reason about data.

References

Resources