Every application that stores data eventually reaches the same point: a database query that does not behave the way you expected. The results are wrong, the query is slow, or a write operation leaves the data in a state you did not intend. In most cases, the problem is not the database. It is a gap in how the developer understands the system underneath.

I have worked on storage systems at both Meta and Microsoft, including Azure storage infrastructure and Facebook’s data layer. At that scale, a poorly written query does not just return wrong results; it affects latency across the entire system. But the fundamentals that prevent those problems are the same ones that apply in a small Express API hitting a PostgreSQL database on a single server.

SQL has been around for fifty years. It outlasted dozens of alternatives and remains the default language for structured data. That kind of staying power is worth taking seriously.

This guide introduces SQL as a language and a way of thinking, not as a reference list of commands. It assumes you have some programming experience, JavaScript or similar, but no prior database knowledge. The progression mirrors how the concepts actually build on each other: from how a database organizes data, to reading it, shaping it, and writing it with care.

1. What SQL is and what it is not

SQL stands for Structured Query Language. It is the standard language for working with relational databases: creating tables, inserting data, retrieving records, and defining relationships between them.

Before anything else, one question worth addressing directly: is SQL a programming language?

Technically, yes. But it is a very different kind of programming language from JavaScript or Python. Those are imperative languages. You write step-by-step instructions that tell the computer exactly how to do something. SQL is declarative. You describe what data you want, and the database engine figures out how to get it.

Here is what that looks like in practice:

SELECT name, email
FROM users
WHERE created_at > '2024-01-01'
ORDER BY name;

You did not write a loop. You did not traverse an index, filter rows one by one, or sort an array. You stated the result you wanted, and the engine handled the rest. This distinction matters because it shapes how you think when writing SQL. Your job is to describe the result set precisely. The engine’s job is to find the most efficient path to it.

This design was not accidental. SQL was developed at IBM in the mid-1970s, built on Edgar Codd’s relational model, which proposed storing data in tables and retrieving it through mathematical set operations. The goal was a language that analysts could use to query data without understanding the underlying storage implementation. Fifty years later, SQL remains the default for structured data, not because the industry lacks alternatives, but because the relational model and its declarative query interface turned out to be a genuinely strong foundation.

SQL is not one thing

When developers talk about learning SQL, they are usually referring to one of several dialects. The core language is standardized (ANSI SQL), but every major database system ships its own implementation with extensions and variations:

• PostgreSQL: Open source, standards-compliant, and feature-rich. The default choice for most new backend projects today.
• MySQL / MariaDB: Widely used, especially in older web stacks. MySQL is owned by Oracle; MariaDB is the open-source fork.
• SQLite: A file-based database with no separate server process. Used heavily in mobile apps, embedded systems, and local development.
• SQL Server: Microsoft’s enterprise database. Common in corporate and .NET environments.

The good news: most of what you learn applies across all of them. The differences show up at the edges: specific functions, performance tuning options, and advanced features. This guide uses standard SQL throughout, with PostgreSQL noted where behavior differs.

2. How a relational database organizes data

Before you write a single query, you need a clear picture of how a relational database stores data. The structure is intentional, and understanding it makes everything that follows easier to reason about.

Tables, rows, and columns

A relational database organizes data into tables. Each table represents one type of thing: users, orders, products, or payments. If you have worked with spreadsheets, the visual is similar: a table has columns across the top and rows of data below.

• A column defines a specific attribute and its data type. A users table might have columns for id, name, email, and created_at.
• A row is a single record. One row in the users table represents one user.
• A cell is the value at the intersection of a row and a column.

Here is what a users table looks like with a few rows of data:

Every row in this table describes one user. Every column holds the same type of information across all rows. This consistency is what makes querying predictable.

Primary keys

Every table needs a way to uniquely identify each row. That is the role of a primary key: a column, or a combination of columns, whose value is unique across every row in the table and never null. In SQL, a PRIMARY KEY constraint enforces both of these rules automatically: it implies UNIQUE and NOT NULL without you having to declare them separately.

In the example above, id is the primary key. No two users share the same id. When another table needs to reference a specific user, it stores that user’s id rather than duplicating the user’s name or email. This avoids redundancy and keeps data consistent across the database.

Most tables use a single integer column as the primary key, often auto-incremented by the database, so you do not have to assign values manually:

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  created_at DATE DEFAULT CURRENT_DATE
);

SERIAL is PostgreSQL’s shorthand for an auto-incrementing integer. Each new row gets the next available value automatically. If you are using MySQL, the equivalent is AUTO_INCREMENT; on SQL Server, it is IDENTITY. The concept is the same across all three: the database manages the value for you.

Data types

Every column in a table has a declared data type. The database enforces that type on every value stored in that column. Choosing the right data type is not a formality: it affects storage size, query performance, and what operations you can perform on the data.

The most common types across SQL dialects:

• Integer types (INT, BIGINT, SERIAL): Whole numbers. Use BIGINT for values that may exceed two billion. Use SERIAL when you want the database to auto-increment the value.
• Decimal types (NUMERIC, DECIMAL): Exact decimal values. Use these for money or any calculation where floating-point rounding errors are unacceptable.
• Floating point (FLOAT, REAL): Approximate decimal values. Faster than NUMERIC but subject to rounding errors. 
• Character types (VARCHAR(n), TEXT): Variable-length strings. VARCHAR(n) enforces a maximum length of n characters. TEXT allows unlimited length in PostgreSQL, but behavior varies by dialect: SQL Server’s TEXT type is deprecated, and MySQL’s TEXT has a 65,535-byte limit. When targeting multiple databases, VARCHAR with an explicit length is the safer choice.
• Boolean (BOOLEAN): True or false values.
• Date and time (DATE, TIMESTAMP): DATE stores a calendar date with no time component. TIMESTAMP stores both date and time.

A small decision here saves debugging time later. Storing a price as TEXT instead of NUMERIC means arithmetic on that column will either fail or produce wrong results. Storing a date as VARCHAR means you cannot sort or compare dates correctly. Let the database enforce the type from the start.

Why one table is never enough

A single table works for simple cases, but real applications have relationships between different types of data. An order belongs to a user. A product belongs to a category. An invoice contains multiple line items.

Putting all of that into one table means repeating data. If a user’s email appears in every row of an orders table, updating that email requires finding and changing every row. Miss one, and the data is inconsistent.

The solution is to split related data into separate tables and connect them with keys. That structure is what makes a database relational: each table has a focused responsibility, and relationships between tables are expressed through shared key values.

3. Your first queries: reading data

The most common thing you will do with SQL is read data. Before you worry about inserting rows, designing schemas, or optimizing performance, you need to be comfortable asking the database questions and understanding what it gives back.

All read operations in SQL start with SELECT.

SELECT and FROM

The two most fundamental clauses in SQL are SELECT and FROM. SELECT tells the database which columns you want. FROM tells it which table to look in.

SELECT name, email
FROM users;

This query returns the name and email columns for every row in the users table. The database does not care about the order of results yet. It just returns every row it finds.

If you want every column without listing them individually, use the wildcard *:

SELECT * 
FROM users;

This is useful when exploring a table you are not familiar with, but avoid it in production code. If the table schema changes and a new column is added, SELECT * returns it automatically, which can break application code that expects a fixed set of columns.

Filtering rows with WHERE

Returning every row in a table is rarely what you want. The WHERE clause filters the result to only rows that match a condition:

SELECT name, email
FROM users
WHERE created_at > '2024-01-01';

This returns only users created after January 1, 2024. The condition after WHERE is evaluated for each row. Rows where the condition is true are included; rows where it is false are excluded.

You can filter on any column, not just the ones you are selecting:

SELECT name, email
FROM users
WHERE id = 3;

This returns the name and email of the user whose id is 3, even though id does not appear in the result.

Combining conditions with AND, OR, and NOT

A single condition is often not enough. SQL lets you combine conditions using logical operators:

-- AND: both conditions must be true
SELECT name, email
FROM users
WHERE created_at > '2024-01-01'
  AND email LIKE '%@example.com';

Note on LIKE and wildcards: % matches any sequence of characters; _ matches exactly one. So ‘%@example.com‘ matches any email ending with @example.com.

-- OR: at least one condition must be true
SELECT name, email
FROM users
WHERE id = 1
  OR id = 2;

-- NOT: excludes rows where the condition is true
SELECT name, email
FROM users
WHERE NOT email LIKE '%@example.com';

When combining AND and OR in the same query, use parentheses to make the logic explicit. Without them, SQL applies AND before OR, which can produce results you did not intend:

-- Without parentheses: AND is evaluated first
SELECT name FROM users
WHERE id = 1 OR id = 2 AND created_at > '2024-01-01';

-- With parentheses: intention is clear
SELECT name FROM users
WHERE (id = 1 OR id = 2) AND created_at > '2024-01-01';

Sorting results with ORDER BY

By default, SQL does not guarantee the order of rows in a result set. If you need a specific order, use ORDER BY:

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

DESC sorts from the largest value to the smallest, so the most recently created users appear first. ASC (ascending) is the default and sorts from smallest to largest.

You can sort by multiple columns. The database sorts by the first column, then breaks ties using the second:

SELECT name, email, created_at
FROM users
ORDER BY created_at DESC, name ASC;

Limiting results with LIMIT

When a table has thousands or millions of rows, returning all of them is expensive and usually unnecessary. LIMIT caps the number of rows the query returns:

SELECT name, email
FROM users
ORDER BY created_at DESC
LIMIT 10;

This returns the ten most recently created users. LIMIT is almost always paired with ORDER BY. Without it, the database returns an arbitrary set of rows up to the limit, which is rarely useful.

For pagination, pair LIMIT with OFFSET:

SELECT name, email
FROM users
ORDER BY created_at DESC
LIMIT 10
OFFSET 20;

This skips the first 20 rows and returns the next 10, which corresponds to page 3 of a 10-items-per-page list. OFFSET-based pagination has performance trade-offs at large offsets, but it is the right starting point for most applications.

Dialect note: SQL Server uses SELECT TOP 10 ... instead of LIMIT. MySQL supports LIMIT but also accepts the shorthand LIMIT 20, 10 for offset pagination.

Putting it together

Here is a query that uses everything from this section:

SELECT name, email, created_at
FROM users
WHERE created_at > '2024-01-01'
  AND email LIKE '%@example.com'
ORDER BY created_at DESC
LIMIT 5;

The database executes this query in a specific order: start with the table (FROM), filter the rows (WHERE), select and compute the columns (SELECT), sort what remains (ORDER BY), then cap the result (LIMIT). This execution order becomes important in the next sections, and Section 5 covers it in detail.

4. Shaping and summarizing data

Reading individual rows gets you only so far. In real applications, you often need answers to questions like: how many users signed up this month, what is the total revenue from completed orders, or which product category has the highest average order value. These questions require collapsing many rows into a summary. That is what aggregate functions and GROUP BY are for.

Aggregate functions

An aggregate function takes a set of rows and computes a single value from them. SQL has five core aggregate functions:

• COUNT: counts the number of rows
• SUM: adds up the values in a numeric column
• AVG: returns the average of a numeric column
• MIN: returns the smallest value in a column
• MAX: returns the largest value in a column

Here is each one in use against an orders table:

-- How many orders exist in total?
SELECT COUNT(*) AS total_orders
FROM orders;

-- What is the total revenue from all completed orders?
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed';

-- What is the average order value?
SELECT AVG(amount) AS average_order_value
FROM orders;

-- What are the smallest and largest order amounts?
SELECT MIN(amount) AS smallest_order,
       MAX(amount) AS largest_order
FROM orders;

COUNT(*) counts every row, including rows that have null values in some columns. COUNT(column_name) counts only rows where that specific column is not null. This behavior is consistent across PostgreSQL, MySQL, and SQL Server and is part of the SQL standard. The distinction matters when a column is sparsely populated: if 20 rows exist but only 15 have a value in the column you are counting, COUNT(column_name) returns 15, not 20.

Grouping rows with GROUP BY

Aggregate functions applied to a whole table give you one number. GROUP BY lets you break that number down by category, so you get one result per group instead of one result overall.

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

This returns one row per distinct value of status, with a count of how many orders share that status:

The rule with GROUP BY is strict: every column in your SELECT list must either be included in the GROUP BY clause or be wrapped in an aggregate function. If you try to select a column that is neither, the database will return an error. The reason is logical: if you are collapsing many rows into one group, the database has no way to know which row’s value to use for an ungrouped column.

-- This will error in PostgreSQL and SQL Server:
-- name is neither grouped nor aggregated
SELECT name, status, COUNT(*)
FROM orders
GROUP BY status;

-- Correct: group by every non-aggregated column in SELECT
SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

Dialect note: MySQL without ONLY_FULL_GROUP_BY enabled allows ungrouped columns in SELECT, silently returning an arbitrary row’s value. Verify this mode is enabled to get standard SQL behavior. PostgreSQL and SQL Server enforce the strict rule by default.

Filtering groups with HAVING

WHERE filters rows before they are grouped. Once rows are grouped and aggregated, you need HAVING to filter on the result of an aggregate function.

-- Which customers have placed more than 5 orders?
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

HAVING COUNT(*) > 5 uses the full aggregate expression, which is the correct approach and works consistently across PostgreSQL, MySQL, and SQL Server.

A practical way to remember the difference: WHERE works on raw rows, HAVING works on grouped results. You can use both in the same query, and they each do their job at different stages:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

This first filters to only completed orders (WHERE), then groups by customer, then keeps only customers with more than five completed orders (HAVING). Section 5 covers why this order of operations matters at the engine level.

Removing duplicates with DISTINCT

DISTINCT removes duplicate values from a result set. Used without aggregation, it returns one row per unique combination of the selected columns:

-- What unique statuses exist in the orders table?
SELECT DISTINCT status
FROM orders;

DISTINCT can also be used inside COUNT to count unique values rather than all values:

-- How many unique customers have placed at least one order?
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Renaming output columns with AS

AS assigns an alias to a column in the result set, which makes output easier to read and is necessary when the column name would otherwise be an expression like COUNT(*):

SELECT
  status,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_revenue,
  AVG(amount)     AS average_order_value
FROM orders
GROUP BY status;

Aliases defined in SELECT are not available in WHERE or HAVING in the same query. This is standard SQL behavior and applies in PostgreSQL and SQL Server. MySQL allows aliases in HAVING (but not WHERE) in some versions, making it a subtle portability risk if you write queries that need to run across multiple databases. 

The safe habit across all dialects is to repeat the full expression in HAVING rather than referencing an alias. This is a direct consequence of SQL’s execution order: WHERE and HAVING run before SELECT assigns aliases. Section 5 explains this in full. If you try to filter using a SELECT alias in WHERE, the database will return an error stating the column does not exist.

5. How SQL actually executes your query

One of the most common sources of confusion in SQL is writing a query that looks correct but produces an error or unexpected results. The column does not exist. The alias is not recognized. The filter is not doing what you expected. In most cases, the cause is the same: the query was written in one order, but the database executes it in a different one.

Understanding that gap clears up a whole category of bugs.

The order you write vs. the order SQL runs

You write a SQL query in this order:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC
LIMIT 10;

That feels natural. You are thinking: “I want to select these columns from this table, where this condition is true.” But the database does not read your query the way you read a sentence. It processes clauses in a fixed logical order regardless of how you wrote them.

The written order follows a different mnemonic: “Skilled Farmers Work Growing Healthy Organic Lettuce” (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT). This is how the clauses appear in the query, but not how they execute.

The actual execution order is different. A helpful mnemonic for the execution order is “Five Wise Guys Happily Share Olive Lasagna” (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT). Here is what happens at each step:

The database’s actual execution path may differ based on its query optimizer, but the results are always consistent with the logical order shown above.

Why does this explain common errors?

• Why you cannot use a SELECT alias in WHERE:

-- This errors: order_count does not exist yet at the WHERE stage
SELECT COUNT(*) AS order_count
FROM orders
WHERE order_count > 5;

-- Correct: use the full expression, or move the
-- filter to HAVING after GROUP BY
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

At step 2 (WHERE), the database has not reached step 5 (SELECT) yet. The alias order_count does not exist at the point WHERE runs. This is not a quirk: it is the direct consequence of execution order.

• Why WHERE and HAVING filter at different stages:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

WHERE status = 'completed' runs at step 2, before any grouping. It eliminates rows that do not match. HAVING COUNT(*) > 5 runs at step 4, after groups have been formed. It eliminates groups that do not match. Using HAVING instead of WHERE to filter raw rows is not wrong syntactically, but it forces the database to group all rows first and then discard the ones that could have been filtered earlier, which is less efficient.

• Why ORDER BY can reference a SELECT alias, but WHERE cannot:

-- This works: ORDER BY runs after SELECT assigns the alias
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

ORDER BY runs at step 6, after SELECT at step 5 has already assigned aliases. So referencing order_count in ORDER BY is valid. The same alias in WHERE at step 2 is not, because SELECT has not run yet.

Dialect note: In SQL Server, a SELECT alias in ORDER BY must stand alone. ORDER BY order_count + 1 is not supported; use the full expression instead.

A single query, annotated

Here is the full execution order applied to one realistic query:

SELECT                              -- Step 5: compute output, assign aliases
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders                         -- Step 1: start with this table
WHERE status = 'completed'          -- Step 2: keep only completed orders
GROUP BY customer_id                -- Step 3: one group per customer
HAVING COUNT(*) > 5                 -- Step 4: keep customers with 6+ orders
ORDER BY total_spent DESC           -- Step 6: sort by highest spenders first
LIMIT 10;                           -- Step 7: return top 10 only

Reading a query this way, in execution order rather than written order, makes it easier to reason about what data exists at each stage and why certain references are valid or not.

6. Writing data: INSERT, UPDATE, DELETE

So far, every query in this guide has been a read operation. The database has not changed. This section covers the write side of SQL: adding new rows, modifying existing ones, and removing rows you no longer need.

These three operations are straightforward to write but carry more risk than SELECT. A read query that is wrong returns bad results. A write query that is wrong changes or destroys data. The habits in this section exist for that reason.

Adding rows with INSERT

INSERT INTO adds one or more rows to a table. You specify the table name, the columns you are inserting into, and the values for each column:

INSERT INTO users (name, email, created_at)
VALUES ('Grace Hopper', 'grace@example.com', '2024-06-01');

Always list the column names explicitly. Omitting them and relying on column order works only as long as the table schema never changes. If a column is added, removed, or reordered later, an implicit insert breaks silently or errors in ways that are hard to trace.

You can insert multiple rows in a single statement by adding more value groups:

INSERT INTO users (name, email, created_at)
VALUES
  ('Ada Lovelace',   'ada@example.com',    '2024-06-02'),
  ('Alan Turing',    'alan@example.com',   '2024-06-03'),
  ('Claude Shannon', 'claude@example.com', '2024-06-04');

Batching inserts this way is significantly faster than running a separate INSERT for each row, because each statement carries a round-trip cost to the database.

If a column has a default value defined in the schema, you can omit it from the insert and the database will fill it in:

-- created_at has DEFAULT CURRENT_DATE in the schema,
-- so it can be omitted
INSERT INTO users (name, email)
VALUES ('Margaret Hamilton', 'margaret@example.com');

Modifying rows with UPDATE

UPDATE changes the values of one or more columns in existing rows:

UPDATE users
SET email = 'grace.hopper@example.com'
WHERE id = 2;

SET specifies which columns to change and what to set them to. WHERE determines which rows are affected. You can update multiple columns in a single statement:

UPDATE users
SET email = 'ada.lovelace@example.com',
    name  = 'Ada Lovelace (updated)'
WHERE id = 1;

The most dangerous mistake in SQL

Running UPDATE or DELETE without a WHERE clause affects every row in the table:

-- This updates every single row in the users table
UPDATE users
SET email = 'oops@example.com';

-- This deletes every single row in the users table
DELETE FROM users;

Neither of these statements produces a warning before executing. The database does exactly what you asked. This is one of the most common causes of data loss in development and production environments alike.

Two habits prevent this:

1. Always write the WHERE clause first. Write UPDATE users SET email = '...' WHERE id = 2 in that order, not UPDATE users SET email = '...' and then add the WHERE as an afterthought. Treating WHERE as optional trains you to forget it.
2. Run a SELECT with the same WHERE clause before writing. Before executing an UPDATE or DELETE, confirm the rows it will affect:

-- Check which rows will be affected first
SELECT id, name, email
FROM users
WHERE id = 2;

-- Then run the UPDATE with the same WHERE clause
UPDATE users
SET email = 'grace.hopper@example.com'
WHERE id = 2;

If the SELECT returns the rows you expect, the UPDATE or DELETE with the same WHERE clause will affect exactly those rows.

Removing rows with DELETE

DELETE FROM removes rows from a table:

DELETE FROM users
WHERE id = 3;

Like UPDATE, DELETE without a WHERE clause removes every row. The table structure and its columns remain intact, but all data is gone.

If you want to remove all rows from a table as a deliberate operation, TRUNCATE is faster than DELETE without a WHERE clause because it does not scan and log each row individually:

-- Removes all rows, faster than DELETE with no WHERE
TRUNCATE TABLE users;

TRUNCATE comes with important constraints. In PostgreSQL, it cannot be used when foreign key constraints reference the table unless you also truncate the referencing tables in the same statement. MySQL and SQL Server handle this differently: MySQL silently disables the foreign key check during truncation in some configurations, and SQL Server requires foreign key constraints to be dropped or disabled first. If your table is referenced by other tables, DELETE with a WHERE clause or a carefully ordered TRUNCATE across related tables is the safer choice.

Resources for a deeper dive

The official PostgreSQL documentation, the MySQL documentation, and the Microsoft SQL Server documentation should always be your primary references for dialect-specific behavior. The PostgreSQL docs in particular are among the best-written technical references in open source.

For hands-on practice, SQLZoo and SQLBolt offer free, browser-based exercises, while LeetCode’s SQL track provides progressively harder problems, once you are comfortable with the basics. For interview prep, Educative’s SQL Interview Patterns is an excellent resource for mastering the logic behind complex queries. Finally, if you want to format queries during development, SQLFormat.org is a straightforward tool to keep handy.

For readers who learn best by writing, reading, and running code, structured interactive courses can provide a head start and help build momentum. Here are a few recommendations:

• Learn SQL for Developers: A beginner-level course that walks through creating databases, retrieving and manipulating data, filtering, sorting, aggregate functions, and joins, with hands-on exercises and real-world examples throughout. A direct continuation of what this guide covers.
• The Art of PostgreSQL: An advanced course focused on PostgreSQL specifically, covering SQL integration, window functions, data modeling, additional data types, and extensions. The right next step if PostgreSQL is your database of choice and you want to go well beyond the basics.
• Mastering Advanced SQL Techniques with MySQL: Covers advanced MySQL topics, including recursive queries, window functions, JSON and XML data handling, and spatial data management. A strong choice if your stack uses MySQL and you want to move into more complex query patterns.