Learn the basics through hands-on practice, not just reading theory. Absolutely no prior coding experience required — work through ready-made examples, then practice as you go with the built-in SQL sandbox.
Curious how this guide came together? See the roadmap.
BASICS
Foundations
01
What is a database?
A system for managing data that stores it reliably, retrieves it efficiently, and allows it to be manipulated systematically. Databases power web and mobile apps as well as data analysis and reporting.
Data is organized into tables made up of rows and columns — each row is an individual entity or event, each column is a specific attribute or property.
02
How databases are organized
Datasets / schemas — groups of related tables, usually around a subject (e.g. ecommerce, finance)
Tables — where the actual data lives, each with defined columns and data types
Columns — the individual fields within a table
Every interaction with a database involves two systems: the database server (stores and runs the database, lives on a network or in the cloud) and the database client (the software used to connect, write queries, and view results). The SQL you write is identical regardless of client — the client is just the window you type through.
03
SQL dialects, clients & connecting
PostgreSQL, MySQL, BigQuery, and SQL Server are the most common dialects — mostly similar, with some differences:
Row limits: Postgres / MySQL / BigQuery use LIMIT 10; SQL Server uses SELECT TOP(10)
Case sensitivity: BigQuery treats table and dataset names as case-sensitive (orders ≠ Orders), but column names are not; Postgres, MySQL, and SQL Server are not case-sensitive for column/table names
Clients come in two forms: web applications (browser-based, nothing to install — e.g. BigQuery Console, Snowflake Web UI) and desktop applications (installed software — e.g. DBeaver, TablePlus, DataGrip).
To connect to a traditional database you'll typically need a server address, username & password, and a database name — usually provided by a DBA or IT team.
04
Selecting columns — SELECT / FROM
SELECT specifies which columns to retrieve, FROM specifies which table to retrieve them from.
select.sql
-- All columns
SELECT *
FROM table_name;
-- Specific columns
SELECT column_1, column_2
FROM table_name;
Query results are temporary snapshots — running SELECT never changes the underlying data. Save the output separately if you need to keep it.
05
Unique values — DISTINCT
Retrieves only unique values from a column, eliminating duplicates. Placed immediately after SELECT.
distinct.sql
SELECT DISTINCT column_1
FROM table_name;
06
Column aliases — AS
Assigns a temporary, more readable name to a column in the results. Doesn't change the original column name in the database. Good aliases are descriptive, consistent, and use snake_case.
alias.sql
SELECT DISTINCT column_1 AS alias_name
FROM table_name;
07
Filtering rows — WHERE
Keeps only the rows that match a condition — evaluated row by row, before any grouping happens. Combine conditions with AND / OR.
where.sql
SELECT column_1, column_2
FROM table_name
WHERE column_1 > 100
AND column_2 = 'value';
Comparison: = != > < >= <=
IN (...) — matches against a list of values
BETWEEN ... AND ... — matches a range
LIKE — pattern matching on text (e.g. 'A%')
IS NULL / IS NOT NULL — matches missing values
WHERE runs before grouping, so it can't filter on an aggregate result (e.g. SUM(column_1) > 100) — that needs HAVING instead, which runs after GROUP BY. See Filtering groups — HAVING.
LIKE's case-sensitivity varies by dialect: Postgres is case-sensitive by default (use ILIKE for case-insensitive matching); MySQL and SQL Server are typically case-insensitive by default, depending on the column's collation.
08
Sorting — ORDER BY
Sorts rows by a specific column.
order.sql
SELECT column_1, column_2
FROM table_name
ORDER BY column_1 DESC; -- highest → lowest (Z–A, 9–0)
-- omit DESC for ascending (lowest → highest)
09
Row limits — LIMIT
Restricts the number of rows returned. Always placed at the end of the query. Limiting is good practice — it prevents slow, expensive queries and guards against accidentally returning far more data than expected.
limit.sql
SELECT column_1, column_2
FROM table_name
ORDER BY column_1 DESC
LIMIT 10;
SELECT column_1, column_2
FROM table_name
ORDER BY column_1 DESC
LIMIT 10;
SELECT TOP (10) column_1, column_2
FROM table_name
ORDER BY column_1 DESC;
SQL Server has no LIMIT — TOP goes right after SELECT instead, and there's no separate clause at the end.
10
Standard clause order
The order clauses are typically written in:
order-of-ops.sql
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...
Style convention: keywords (SELECT, FROM, DISTINCT, etc.) are written in UPPERCASE. The database accepts lowercase too, but uppercase is the widely-adopted professional standard — it makes keywords stand out from table/column names at a glance.
11
Good habits
SQL is syntax-sensitive — a misplaced comma or misspelled column name will throw an error, so read error messages carefully and use an editor with syntax highlighting or autocomplete
SELECT queries are read-only — they return a snapshot of the data without modifying the underlying tables
Add LIMIT while exploring an unfamiliar table, to avoid returning more rows than needed
PRACTICE
Try it yourself
Real queries, checked live against the Playground's coffee-shop dataset.
SELECT
COUNT(column_1) AS record_count,
COUNT(DISTINCT column_2) AS unique_count,
SUM(column_3) AS total_amount,
AVG(column_3) AS avg_amount,
MIN(column_3) AS min_amount,
MAX(column_3) AS max_amount
FROM table_name;
SUM — total of all values
AVG — average value
MIN / MAX — smallest / largest value
COUNT — number of rows or values
COUNT(DISTINCT ...) — number of unique values
02
Grouping data — GROUP BY
Runs aggregate functions separately for each value in a grouping column. It's good practice to first check the distinct values of the grouping column.
group-by.sql
-- Check the grouping values first
SELECT DISTINCT grouping_column
FROM table_name;
-- Then aggregate by group
SELECT
grouping_column,
COUNT(column_1) AS record_count,
SUM(column_2) AS total_amount
FROM table_name
WHERE column_3 > 0
GROUP BY grouping_column
ORDER BY total_amount DESC;
WHERE filters rows before they're grouped. To filter after aggregation — e.g. only groups where total_amount > 1000 — use HAVING instead, covered next.
03
Filtering groups — HAVING
GROUP BY collapses rows into groups, and the aggregate values (COUNT, SUM, AVG, etc.) don't exist yet at the point WHERE runs — so WHERE can't filter on them. HAVING runs after grouping and aggregation, so it can filter on the aggregated result itself.
having.sql
SELECT
grouping_column,
COUNT(column_1) AS record_count,
SUM(column_2) AS total_amount
FROM table_name
WHERE column_3 > 0
GROUP BY grouping_column
HAVING SUM(column_2) > 1000
ORDER BY total_amount DESC;
Order of execution: WHERE → GROUP BY → HAVING → ORDER BY. WHERE filters individual rows before grouping; HAVING filters entire groups after aggregation — and unlike WHERE, it can reference aggregate functions directly.
04
Multiple grouping columns
Always include every grouping column in the SELECT list.
group-multi.sql
SELECT
grouping_column_1,
grouping_column_2,
COUNT(column_1) AS record_count,
SUM(column_2) AS total_amount
FROM table_name
GROUP BY grouping_column_1, grouping_column_2
ORDER BY grouping_column_1 ASC, total_amount DESC;
Group numbers can be used instead of repeating column names, e.g. GROUP BY 1, 2 — but this has a limitation: it silently breaks or points at the wrong column if the SELECT list is reordered later, so it's more fragile than naming columns explicitly.
group-by-ordinal.sql
SELECT grouping_column_1, grouping_column_2, COUNT(*) AS record_count
FROM table_name
GROUP BY 1, 2;
SELECT grouping_column_1, grouping_column_2, COUNT(*) AS record_count
FROM table_name
GROUP BY 1, 2;
-- GROUP BY 1, 2 is NOT supported here — it's treated as a constant,
-- silently grouping every row into a single group instead of erroring.
SELECT grouping_column_1, grouping_column_2, COUNT(*) AS record_count
FROM table_name
GROUP BY grouping_column_1, grouping_column_2;
SQL Server is the odd one out: unlike Postgres and MySQL, it doesn't support ordinal GROUP BY at all — and it fails silently rather than throwing an error, so this is worth knowing before it costs you a wrong result.
05
Handling missing groups
GROUP BY only produces rows for combinations that actually exist in the data — it can't invent a row for a group that isn't present. If an expected group is missing from the results, it's worth checking:
Whether the data is actually missing — a join or filter earlier in the query may have dropped rows it shouldn't have
Whether that combination simply never occurred — the group is valid but had no activity, so its absence is correct
Whether the combination is impossible — the data's structure rules it out entirely
06
Data transformations
Multiple transformations can be chained in one SELECT, separated by commas. Standard arithmetic operators (+ - * /) are all available.
transform.sql
SELECT
*,
column_1 * 0.92 AS column_1_adjusted,
column_2 + column_3 AS combined_total
FROM table_name;
When in doubt, add parentheses — they cost nothing and prevent subtle bugs in the order of operations.
07
Conditional logic — CASE WHEN
Evaluates a list of conditions in order and returns a value for the first one that's true — SQL's equivalent of if/else. Works anywhere an expression can go: SELECT, WHERE, ORDER BY, even inside an aggregate.
case-when.sql
SELECT
*,
CASE
WHEN column_1 >= 100 THEN 'high'
WHEN column_1 >= 50 THEN 'medium'
ELSE 'low'
END AS column_1_tier
FROM table_name;
Conditions are checked top to bottom and the first match wins — order them from most to least specific. ELSE is optional; without it, non-matching rows get NULL instead of an explicit fallback.
08
Calculating ratios safely — NULLIF
Ratios are calculated by dividing one column by another. Division by zero is a common error — wrap the denominator in NULLIF to avoid it (it returns NULL instead of erroring when the denominator is zero).
ratios.sql
SELECT
*,
100 * column_2 / NULLIF(column_1, 0) AS ratio_pct
FROM table_name
ORDER BY column_1;
09
NULL handling — COALESCE
Takes a list of expressions and returns the first one that isn't NULL, checked left to right. Most often used to swap in a default or fallback value wherever a column might be missing data.
coalesce.sql
SELECT
*,
COALESCE(column_1, column_2, 0) AS column_1_filled
FROM table_name;
Think of it as the reverse of NULLIF: NULLIF turns a value into NULL under a condition, COALESCE turns NULL into a real value. They're often used together — NULLIF to guard a division, COALESCE to fill the NULL result with something display-friendly.
10
The WITH clause (CTEs)
Creates a named temporary result from a query. The temporary table contains all original columns plus any new calculated columns. The final SELECT queries from the temporary table, not the original — and can reuse column aliases from the WITH step directly, with no repetition needed. One of the most useful tools in the SQL toolkit.
with-clause.sql
WITH temp_table AS (
SELECT
*,
column_1 * 0.92 AS column_1_adjusted
FROM table_name
)
SELECT *
FROM temp_table
WHERE column_1_adjusted > 100;
Syntax is identical across Postgres, MySQL, and SQL Server — but MySQL only supports CTEs from version 8.0 onward. Earlier versions don't support WITH at all.
11
Date & time functions
Getting the current date, pulling a piece out of a date (year, month, weekday...), and shifting a date forward or backward are everyday needs — and one of the areas where the three dialects genuinely part ways syntactically.
date-basics.sql
SELECT
CURRENT_DATE AS today,
EXTRACT(YEAR FROM column_1) AS column_1_year,
column_1 + INTERVAL '7 days' AS week_later,
column_1 - INTERVAL '1 month' AS month_earlier
FROM table_name;
SELECT
CURDATE() AS today,
EXTRACT(YEAR FROM column_1) AS column_1_year,
DATE_ADD(column_1, INTERVAL 7 DAY) AS week_later,
DATE_SUB(column_1, INTERVAL 1 MONTH) AS month_earlier
FROM table_name;
SELECT
CAST(GETDATE() AS DATE) AS today,
DATEPART(year, column_1) AS column_1_year,
DATEADD(DAY, 7, column_1) AS week_later,
DATEADD(MONTH, -1, column_1) AS month_earlier
FROM table_name;
Current date/time — CURRENT_DATE (Postgres), CURDATE()/NOW() (MySQL), GETDATE() (SQL Server)
Extracting a part — EXTRACT(part FROM date) works in both Postgres and MySQL; SQL Server uses DATEPART(part, date) (or the shorthand YEAR()/MONTH()/DAY())
Shifting a date — Postgres uses plain +/- with an INTERVAL literal; MySQL has dedicated DATE_ADD()/DATE_SUB() functions; SQL Server's single DATEADD() function handles both directions via a negative number
date-diff.sql
-- Subtraction returns the gap directly (an integer number of days)
SELECT column_2 - column_1 AS days_between
FROM table_name;
-- Two-argument form, always in days: end date first
SELECT DATEDIFF(column_2, column_1) AS days_between
FROM table_name;
-- Three-argument form, unit first: start date, then end date
SELECT DATEDIFF(day, column_1, column_2) AS days_between
FROM table_name;
Watch the argument order here — it's a classic source of sign-flipped bugs. MySQL's two-argument DATEDIFF(end, start) puts the later date first; SQL Server's three-argument DATEDIFF(unit, start, end) puts the earlier date first. Mixing the two conventions up silently returns a negative number instead of an error.
12
String functions
Combining, measuring, and cleaning up text values — the function names are mostly shared across dialects, but string concatenation is the one place they genuinely diverge.
concat.sql
SELECT column_1 || ' - ' || column_2 AS combined
FROM table_name;
-- MySQL's || means logical OR by default, not concatenation
SELECT CONCAT(column_1, ' - ', column_2) AS combined
FROM table_name;
SELECT column_1 + ' - ' + column_2 AS combined
FROM table_name;
The CONCAT() function itself is the portable choice — it works in all three dialects (Postgres and SQL Server support it too, alongside their native operators) and, unlike Postgres's || or SQL Server's +, treats a NULL argument as an empty string rather than making the whole result NULL.
UPPER / LOWER — change case
TRIM — strip leading/trailing whitespace (LTRIM/RTRIM for one side only)
SUBSTRING(string, start, length) — extract part of a string; consistent across all three
REPLACE(string, old, new) — swap all occurrences of a substring; consistent across all three
String length — LENGTH() in Postgres and MySQL, LEN() in SQL Server
One gotcha on that last one: SQL Server's LEN() ignores trailing spaces, while Postgres and MySQL's LENGTH() counts them — LEN('hi ') returns 2, LENGTH('hi ') returns 4.
PRACTICE
Try it yourself
Real queries, checked live against the Playground's coffee-shop dataset.
A join combines rows from two or more tables based on a related column between them — a common key. The general pattern: alias each table (commonly to a single letter), then join ON the columns that relate them.
join-pattern.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
JOIN table_b AS b
ON a.key_column = b.key_column;
Table aliases (a, b) keep joined queries readable — especially once both tables have overlapping column names and every reference needs to say which table it comes from.
02
INNER JOIN
Returns only the rows that have a matching value in both tables. Rows with no match on either side are excluded entirely.
inner-join.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
INNER JOIN table_b AS b
ON a.key_column = b.key_column;
INNER JOIN is the default — writing JOIN on its own means INNER JOIN.
03
LEFT JOIN
Returns all rows from the left (first) table, plus matching rows from the right table. Where there's no match, the right table's columns come back as NULL.
left-join.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.key_column = b.key_column;
To isolate rows in table_a with no match in table_b, add WHERE b.key_column IS NULL — a common pattern for finding unmatched or "orphaned" records.
04
RIGHT JOIN
The mirror of LEFT JOIN — returns all rows from the right (second) table, plus matching rows from the left. Where there's no match, the left table's columns come back as NULL.
right-join.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
RIGHT JOIN table_b AS b
ON a.key_column = b.key_column;
Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order — most people default to LEFT JOIN throughout for consistency, and reach for RIGHT JOIN rarely.
05
FULL OUTER JOIN
Returns all rows from both tables — matched where possible, with NULL filled in on whichever side has no match.
full-outer-join.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
FULL OUTER JOIN table_b AS b
ON a.key_column = b.key_column;
-- MySQL has no FULL OUTER JOIN — emulate with UNION
SELECT a.column_1, b.column_2
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.key_column = b.key_column
UNION DISTINCT
SELECT a.column_1, b.column_2
FROM table_a AS a
RIGHT JOIN table_b AS b
ON a.key_column = b.key_column;
SELECT
a.column_1,
b.column_2
FROM table_a AS a
FULL OUTER JOIN table_b AS b
ON a.key_column = b.key_column;
Not supported in MySQL — the UNION version above combines a LEFT JOIN and a RIGHT JOIN to get the same result.
06
CROSS JOIN
Returns every combination of rows from both tables — a Cartesian product. No ON condition and no shared key required.
cross-join.sql
SELECT
a.column_1,
b.column_2
FROM table_a AS a
CROSS JOIN table_b AS b;
Result row count = rows in table_a × rows in table_b — easy to accidentally generate a huge result set. Use deliberately, e.g. to generate every combination of dates and categories.
07
SELF JOIN
Joins a table to itself — useful for comparing rows within the same table, such as hierarchical or relative relationships. Requires two different aliases for the same table.
self-join.sql
SELECT
e.column_1 AS entity,
m.column_1 AS related_entity
FROM table_a AS e
JOIN table_a AS m
ON e.related_key = m.key_column;
Classic example: an employees table where each row has a manager_id pointing to another row's id in that same table.
08
Subqueries
A SELECT nested inside another query. The inner query runs first and its result feeds the outer one — it can appear in the WHERE clause (to filter against a set of values), in the FROM clause (as a derived table), or in the SELECT list (as a single scalar value).
subquery.sql
SELECT
a.column_1,
a.column_2
FROM table_a AS a
WHERE a.column_1 IN (
SELECT b.column_1
FROM table_b AS b
WHERE b.column_3 > 100
);
A non-correlated subquery (like the one above) is fully independent — it runs once, and its result is reused for every row of the outer query. A correlated subquery references a column from the outer query, so it re-runs once per outer row — powerful, but worth watching on large tables since it can get slow. Many correlated subqueries can be rewritten as a JOIN for better performance.
09
UNION / INTERSECT / EXCEPT
Where a JOIN combines tables side by side (horizontally), set operations stack the results of two queries on top of each other (vertically). Both queries must return the same number of columns, in the same order, with compatible data types.
set-operations.sql
-- All rows from both, duplicates removed
SELECT column_1 FROM table_a
UNION
SELECT column_1 FROM table_b;
-- All rows from both, duplicates kept
SELECT column_1 FROM table_a
UNION ALL
SELECT column_1 FROM table_b;
-- Only rows present in both
SELECT column_1 FROM table_a
INTERSECT
SELECT column_1 FROM table_b;
-- Rows in table_a that aren't in table_b
SELECT column_1 FROM table_a
EXCEPT
SELECT column_1 FROM table_b;
MySQL is the odd one out here: it's always supported UNION, but only added INTERSECT and EXCEPT in version 8.0.31 (October 2022) — earlier versions don't have them at all. Postgres and SQL Server have supported all four since long before that.
10
Recursive CTEs
A CTE (see the WITH clause) that references itself, used to walk hierarchical or graph-like data — org charts, category trees, bill-of-materials — where the depth isn't known in advance. It has two parts joined by UNION ALL: an anchor member that provides the starting rows, and a recursive member that references the CTE's own name and adds one more "level" each time it runs, stopping once it produces no new rows.
recursive-cte.sql
WITH RECURSIVE org_chart AS (
-- Anchor: top of the hierarchy
SELECT employee_id, manager_id, 1 AS tier
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: one level down each pass
SELECT e.employee_id, e.manager_id, oc.tier + 1 AS tier
FROM employees AS e
JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
WITH RECURSIVE org_chart AS (
-- Anchor: top of the hierarchy
SELECT employee_id, manager_id, 1 AS tier
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: one level down each pass
SELECT e.employee_id, e.manager_id, oc.tier + 1 AS tier
FROM employees AS e
JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
-- No RECURSIVE keyword needed — SQL Server detects
-- the self-reference and treats the CTE as recursive automatically.
WITH org_chart AS (
SELECT employee_id, manager_id, 1 AS tier
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, oc.tier + 1 AS tier
FROM employees AS e
JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
Postgres and MySQL both require the RECURSIVE keyword after WITH — leaving it off is a common source of "column does not exist" errors on the self-reference. SQL Server is the exception: it infers recursion from the CTE referencing itself, so plain WITH is enough. Always include a condition that eventually stops producing new rows, or the query runs until it hits the engine's recursion limit.
11
PIVOT / UNPIVOT
Reshapes data between a "long" format (one row per fact) and a "wide" format (one row per entity, with facts spread across columns) — PIVOT turns distinct row values into columns, UNPIVOT does the reverse. SQL Server has native PIVOT/UNPIVOT operators; Postgres and MySQL don't, so the same result is built with conditional aggregation instead.
pivot.sql
-- Long → wide, via conditional aggregation
SELECT
product,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2
FROM sales
GROUP BY product;
-- Long → wide, via conditional aggregation
SELECT
product,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2
FROM sales
GROUP BY product;
-- Long → wide, via the native PIVOT operator
SELECT product, [Q1], [Q2]
FROM sales
PIVOT (
SUM(revenue) FOR quarter IN ([Q1], [Q2])
) AS pivoted;
The conditional-aggregation form works identically in Postgres and MySQL, and is arguably more portable than SQL Server's dedicated syntax since it's just SELECT + CASE + GROUP BY — the tradeoff is that every target column has to be spelled out by hand rather than declared in one clause. UNPIVOT follows the same split: SQL Server has the operator natively, Postgres and MySQL rebuild the long format with a UNION ALL of one SELECT per column being unpivoted.
12
Window functions
Run a calculation across a set of rows related to the current one — without collapsing them into a single row the way GROUP BY does. Every input row survives in the output, with the calculated value attached alongside it. The OVER() clause is what makes a function a window function: PARTITION BY splits the rows into independent groups (like GROUP BY, but without hiding any rows), and ORDER BY inside the parentheses controls the order the calculation is applied in within each partition.
ranking.sql
SELECT
grouping_column,
column_1,
ROW_NUMBER() OVER (
PARTITION BY grouping_column ORDER BY column_1 DESC
) AS row_num,
RANK() OVER (
PARTITION BY grouping_column ORDER BY column_1 DESC
) AS rank_num,
DENSE_RANK() OVER (
PARTITION BY grouping_column ORDER BY column_1 DESC
) AS dense_rank_num
FROM table_name;
ROW_NUMBER() — a unique, sequential number per row in the partition (1, 2, 3, 4…), even for ties
RANK() — same rank for ties, then skips the numbers a tie used up (1, 2, 2, 4…)
DENSE_RANK() — same rank for ties, but never skips a number (1, 2, 2, 3…)
LAG() / LEAD() — the value from a previous / following row in the partition, useful for row-over-row comparisons
Most aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be used as window functions too, by adding OVER (...)
running-total.sql
SELECT
order_date,
column_1 AS daily_amount,
SUM(column_1) OVER (
ORDER BY order_date
) AS running_total,
LAG(column_1) OVER (
ORDER BY order_date
) AS previous_day_amount
FROM table_name
ORDER BY order_date;
A running total is just SUM() with an OVER() clause and no PARTITION BY — without a partition, the whole result set is treated as one window, and ORDER BY controls how far the running sum has gotten by each row. Support is broadly consistent across all three dialects today, but window functions are a comparatively recent addition: MySQL added them in 8.0 (2018), and while SQL Server introduced basic OVER()/ranking support back in 2005, LAG/LEAD and the fuller feature set didn't arrive until SQL Server 2012.
13
JSON / semi-structured data
Pulling a value out of a JSON document stored in a column — Postgres and MySQL both offer a compact operator shorthand for this; SQL Server, on its long-standing NVARCHAR-based approach (the stable baseline supported since 2016, and still what most SQL Server installs use today), goes through dedicated functions instead.
json-extract.sql
SELECT
json_column ->> 'status' AS status_value,
json_column -> 'address' ->> 'city' AS city
FROM table_name;
SELECT
json_column ->> '$.status' AS status_value,
json_column ->> '$.address.city' AS city
FROM table_name;
SELECT
JSON_VALUE(json_column, '$.status') AS status_value,
JSON_VALUE(json_column, '$.address.city') AS city
FROM table_name;
In Postgres, -> returns the value still as JSON (useful for chaining into a nested object) while ->> returns it as plain text — MySQL's arrow operators work the same way, just addressed with a JSONPath string ('$.key') instead of a bare key name. SQL Server has no operator shorthand at all: JSON_VALUE() pulls out a single scalar, and its sibling JSON_QUERY() is the equivalent of Postgres/MySQL's -> for pulling out a nested object or array. (SQL Server 2025 adds a native binary json type closer to Postgres's jsonb, but the function-based approach shown here still works on it and remains the portable baseline across SQL Server versions.)
14
INSERT / UPDATE / DELETE
Everything so far has only read data. These three statements write it: INSERT adds new rows, UPDATE changes existing ones, and DELETE removes them. The core syntax for all three is identical across Postgres, MySQL, and SQL Server.
dml-basics.sql
INSERT INTO table_name (column_1, column_2)
VALUES ('value_a', 100);
UPDATE table_name
SET column_2 = 150
WHERE column_1 = 'value_a';
DELETE FROM table_name
WHERE column_1 = 'value_a';
UPDATE and DELETE without a WHERE clause affect every row in the table — always write and check the WHERE clause first, ideally by running the equivalent SELECT first to see exactly which rows will be hit. For wiping every row in a table on purpose, TRUNCATE TABLE table_name is faster than an unqualified DELETE (it deallocates the data pages instead of deleting row by row) but usually can't be rolled back the same way and typically resets any auto-increment counter.
upsert.sql
INSERT INTO table_name (id, column_1)
VALUES (1, 'value_a')
ON CONFLICT (id)
DO UPDATE SET column_1 = excluded.column_1;
INSERT INTO table_name (id, column_1)
VALUES (1, 'value_a')
AS new_row
ON DUPLICATE KEY UPDATE column_1 = new_row.column_1;
MERGE INTO table_name AS tgt
USING (SELECT 1 AS id, 'value_a' AS column_1) AS src
ON tgt.id = src.id
WHEN MATCHED THEN
UPDATE SET column_1 = src.column_1
WHEN NOT MATCHED THEN
INSERT (id, column_1) VALUES (src.id, src.column_1);
"Upsert" — insert a row, or update it if it already exists — is one of the sharpest three-way syntax splits in SQL. Postgres's ON CONFLICT targets a specific unique constraint and uses the pseudo-table EXCLUDED to refer to the row that was being inserted; MySQL's ON DUPLICATE KEY UPDATE checks every unique index on the table rather than one you name explicitly, and refers to the incoming row through an aliased name (the older VALUES() function form is deprecated as of MySQL 8.0.20); SQL Server has no dedicated upsert statement at all and reaches for the general-purpose MERGE, matching source against target rows and branching on whether a match was found.
15
Transactions — COMMIT / ROLLBACK
A transaction bundles multiple statements into one all-or-nothing unit — if anything inside fails, the whole thing can be undone as if none of it happened. Outside of an explicit transaction, all three dialects default to "autocommit": each statement commits on its own the moment it succeeds.
transaction-basics.sql
BEGIN;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
COMMIT;
-- Or, to undo both updates instead: ROLLBACK;
START TRANSACTION;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
COMMIT;
-- Or, to undo both updates instead: ROLLBACK;
BEGIN TRANSACTION;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
COMMIT TRANSACTION;
-- Or, to undo both updates instead: ROLLBACK TRANSACTION;
savepoint.sql
BEGIN;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
SAVEPOINT before_risky_update;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
-- Undo just the second update, keep the first pending:
ROLLBACK TO before_risky_update;
COMMIT;
START TRANSACTION;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
SAVEPOINT before_risky_update;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
-- Undo just the second update, keep the first pending:
ROLLBACK TO before_risky_update;
COMMIT;
BEGIN TRANSACTION;
UPDATE table_name SET column_1 = 'value_a' WHERE id = 1;
SAVE TRANSACTION before_risky_update;
UPDATE table_name SET column_1 = 'value_b' WHERE id = 2;
-- Undo just the second update, keep the first pending:
ROLLBACK TRANSACTION before_risky_update;
COMMIT TRANSACTION;
A savepoint marks a point inside a transaction to roll back to without discarding everything before it. Postgres and MySQL share the same SAVEPOINT / ROLLBACK TO / RELEASE SAVEPOINT vocabulary (the SQL-standard one); SQL Server uses its own SAVE TRANSACTION / ROLLBACK TRANSACTION name instead, and has no equivalent to RELEASE SAVEPOINT — a SQL Server savepoint simply stays available until the transaction ends.
PRACTICE
Try it yourself
Real queries, checked live against the Playground's coffee-shop dataset.
Everything so far has queried or written rows inside tables that already exist. These three statements define the structure itself: CREATE makes a new object (a table, index, view, etc.), ALTER changes an existing one, and DROP removes it entirely. The core syntax is shared across Postgres, MySQL, and SQL Server.
ddl-basics.sql
CREATE TABLE table_name (
id INTEGER PRIMARY KEY,
column_1 VARCHAR(50) NOT NULL,
column_2 INTEGER
);
ALTER TABLE table_name
ADD COLUMN column_3 DATE;
DROP TABLE table_name;
DROP removes the object and all its data immediately and generally can't be rolled back once committed — always double-check the object name first, especially against DROP TABLE vs TRUNCATE TABLE (empties a table but keeps its structure) vs DELETE (removes rows one at a time, can be filtered with WHERE). Also worth knowing: MySQL still autocommits DDL inside a transaction on most setups, so a DDL statement can't always be rolled back the way a DML one can — see Transactions.
02
Constraints — PK, FK, UNIQUE
Constraints are rules attached to a column or table that the database enforces on every write, so bad data can't get in regardless of which application or query inserted it. A primary key uniquely identifies each row and can't be NULL; a foreign key ties a column to a primary key in another table, keeping the two in sync; UNIQUE just forbids duplicate values without the "identifies the row" role of a primary key.
constraints.sql
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
order_code VARCHAR(20) UNIQUE,
quantity INTEGER
);
A table can only have one primary key, but it can span multiple columns (a "composite" key) and can have any number of separate UNIQUE constraints. Foreign keys are also where ON DELETE/ON UPDATE behavior gets defined — e.g. ON DELETE CASCADE to automatically remove child rows when the parent row is deleted, versus the default of blocking the delete while orphaned rows would remain.
03
Indexes & query performance
An index is a separate data structure the database maintains alongside a table, built for fast lookups on specific columns — similar to a book's index letting you jump straight to a page instead of reading cover to cover. Without one, a lookup on a large table means scanning every row.
indexes.sql
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
DROP INDEX idx_orders_customer_id;
Indexes speed up reads but aren't free: every INSERT/UPDATE/DELETE also has to update every index on that table, and each index takes its own disk space. Primary keys and (in most dialects) UNIQUE constraints are indexed automatically — the columns worth adding an index to by hand are the ones frequently used in WHERE, JOIN, or ORDER BY that aren't already covered by one.
04
EXPLAIN / query plans
EXPLAIN asks the database to show its query plan — the actual steps it intends to take to run a query — instead of running it. That's how you confirm whether a slow query is actually using an index or falling back to a full table scan.
explain.sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
SET STATISTICS IO, TIME ON;
GO
SELECT * FROM orders WHERE customer_id = 42;
Postgres and MySQL (8.0.18+) share EXPLAIN ANALYZE, which actually runs the query and reports real timings alongside the plan — plain EXPLAIN (no ANALYZE) estimates the plan without running it, which is safer to use on a write query. SQL Server has no ANALYZE-style keyword; the closest equivalent is enabling SET STATISTICS IO, TIME ON before running the query normally, or reading the visual "Actual Execution Plan" in SSMS. Across all three, the detail worth scanning for first is whether the plan says it's using an index (an "index scan"/"seek") or scanning the whole table ("sequential scan"/"table scan").
05
Views
A view is a saved query that behaves like a read-only virtual table — querying it re-runs the underlying SELECT every time, so it never goes stale, but it also doesn't store data of its own. Useful for hiding a complicated join behind a simple name, or for giving a restricted group of columns to someone without exposing the whole table.
views.sql
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';
SELECT * FROM active_customers;
DROP VIEW active_customers;
Redefining an existing view's query differs by dialect: Postgres and MySQL both support CREATE OR REPLACE VIEW to swap in a new definition without dropping it first; SQL Server has no OR REPLACE form and uses ALTER VIEW instead (or CREATE OR ALTER VIEW as of SQL Server 2016 SP1, which also creates the view if it doesn't already exist). A view generally can't be written through if it involves a join, aggregate, or DISTINCT — an "updatable view" only works for simple single-table views without those.
06
Stored procedures & functions
Both package up reusable SQL logic and save it inside the database itself, so any application or user can call it by name instead of re-sending the same statements. The dividing line — and the exact syntax — differs sharply by dialect.
reusable-logic.sql
CREATE OR REPLACE FUNCTION get_customers_by_status(status_value VARCHAR)
RETURNS SETOF customers
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT * FROM customers WHERE status = status_value;
END;
$$;
SELECT * FROM get_customers_by_status('active');
DELIMITER //
CREATE PROCEDURE get_customers_by_status(IN status_value VARCHAR(20))
BEGIN
SELECT * FROM customers WHERE status = status_value;
END //
DELIMITER ;
CALL get_customers_by_status('active');
CREATE OR ALTER PROCEDURE get_customers_by_status
@status_value VARCHAR(20)
AS
BEGIN
SELECT * FROM customers WHERE status = @status_value;
END;
EXEC get_customers_by_status 'active';
In Postgres, a procedure can't return a result set at all — only a function can, which is why the example above uses FUNCTION/RETURNS SETOF/RETURN QUERY and is invoked with plain SELECT. MySQL and SQL Server draw the line differently: their procedures can return a result set just by running a bare SELECT inside the body, invoked with CALL (MySQL) or EXEC (SQL Server). MySQL also needs the DELIMITER dance shown above so the client doesn't treat the semicolons inside the procedure body as the end of the statement — Postgres and SQL Server don't need this since the body is wrapped in $$...$$ or a single BEGIN...END block instead. One more asymmetry worth knowing: MySQL has no CREATE OR REPLACE PROCEDURE (only MariaDB does) — updating one means DROP PROCEDURE IF EXISTS first, then re-creating it.
PRACTICE
Try it yourself
Real queries, checked live against the Playground's coffee-shop dataset.
Every clause and keyword covered in the guide, on one page, syntax only. Dialect differences are flagged inline where they matter — see the linked page for the full explanation and all three dialects.
SELECT a.col_1, b.col_2
FROM table_a AS a
JOIN table_b AS b ON a.id = b.a_id;
LEFT / RIGHT JOIN
SELECT a.col_1, b.col_2
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.a_id;
FULL OUTER JOIN
SELECT a.col_1, b.col_2
FROM table_a AS a
FULL OUTER JOIN table_b AS b ON a.id = b.a_id;
-- MySQL: UNION of a LEFT and a RIGHT JOIN (no native FULL OUTER)
CROSS / SELF JOIN
SELECT a.col_1, b.col_2 FROM table_a AS a CROSS JOIN table_b AS b;
SELECT e.name, m.name AS manager
FROM employees AS e JOIN employees AS m ON e.manager_id = m.id;
Subqueries
SELECT t.column_1
FROM table_name AS t
WHERE t.column_1 IN (SELECT o.column_1 FROM other_table AS o);
UNION / INTERSECT / EXCEPT
SELECT column_1 FROM table_a
UNION
SELECT column_1 FROM table_b;
-- INTERSECT / EXCEPT: same shape, different set operator
Recursive CTE
WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, 1 AS tier
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, oc.tier + 1 AS tier
FROM employees AS e
JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
-- SQL Server: just WITH, no RECURSIVE keyword
PIVOT / UNPIVOT
-- Postgres/MySQL: conditional aggregation
SELECT SUM(CASE WHEN category='coffee' THEN price END) AS coffee
FROM products;
-- SQL Server has native PIVOT / UNPIVOT operators
Window functions
SELECT column_1,
ROW_NUMBER() OVER (PARTITION BY column_2 ORDER BY column_1) AS rn,
SUM(column_1) OVER (ORDER BY column_1) AS running_total
FROM table_name;
CREATE TABLE table_name (id INTEGER PRIMARY KEY, col_1 TEXT);
ALTER TABLE table_name ADD COLUMN col_2 TEXT;
DROP TABLE table_name;
Constraints
id INTEGER PRIMARY KEY,
col_1 TEXT NOT NULL UNIQUE,
other_id INTEGER REFERENCES other_table(id) ON DELETE CASCADE
Indexes
CREATE INDEX idx_name ON table_name (column_1);
EXPLAIN
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column_1 = 1;
-- SQL Server: SET STATISTICS IO, TIME ON;
Views
CREATE VIEW view_name AS
SELECT column_1 FROM table_name WHERE column_1 > 100;
-- redefine: Postgres/MySQL CREATE OR REPLACE VIEW; SQL Server ALTER VIEW
Stored procedures
-- Postgres: CREATE FUNCTION ... RETURNS SETOF ...; call via SELECT
-- MySQL/SQL Server: CREATE PROCEDURE ...; call via CALL / EXEC
ROADMAP
More to come
v2.8
All 19 topics originally listed here have shipped — Basics, Intermediate, Advanced, and Schema now cover the full original roadmap. Since then this has grown into an ongoing public resource — changelog of what's shipped, and what's planned next, below.
Changelog
Last updated August 2026. Version numbers track each build in shipping order.
v2.1Conditional logic & filtering — CASE WHEN, NULL handling (COALESCE), HAVING
v2.2Subqueries, set operations & reshaping data — Subqueries, Recursive CTEs, UNION/INTERSECT/EXCEPT, PIVOT/UNPIVOT
v2.7Reusable database objects — Views, Stored procedures & functions — all 19 original topics now shipped
v2.8Interactive learning — practice exercises on Basics/Intermediate/Advanced/Schema, checked live against the Playground, plus a single-page syntax Cheat Sheet
Planned next
09Usability, navigation & content audit — page reordering/splits, a glossary for undefined terms, JOIN-type diagrams
10Quickstart: your first table — a short standalone walkthrough of creating a table and loading a few rows
11Database design fundamentals — data types, normalization, ER modeling & relationships