SQL basics: from your first table to a result you can explain
A query can run successfully and answer the wrong question. I work through six orders to show how selection, grouping, missing values and result checks fit together.
Knowledge base / SQL
SQL: commands and functions with examples
SQL commands and functions used in analysis: filtering, grouping, JOINs, window functions, dates and text, with notes on engine differences.
99 entries
The SQL entries describe the commands and functions that appear in typical analytical queries: filtering, grouping, JOINs, window functions, dates and text. The basics, such as SELECT, WHERE and GROUP BY, behave similarly in most databases. Date, text and JSON functions do not. The same operation in PostgreSQL, SQL Server, MySQL and SQLite often has a different name or different arguments.
For engine-specific functions, check which dialect an example targets. Before you run a query on a large table, test it on a few rows where you know the correct result. With JOINs and aggregations in particular, such a test quickly reveals duplicated rows or lost NULL values.
If you are starting out, work through “Query basics”, “Aggregations” and “JOINs and set operations” in that order. The articles below the grid show fuller examples that combine several of these elements.
Query basics 7
Aggregations 7
JOINs and set operations 11
Conditions and NULL 5
Window functions 9
Dates and time 14
Text 19
Numeric functions 10
Types, JSON and database objects 17
No matching entries. Try a shorter name.
Articles that go further
SQL JOIN types: how I combine tables and check the result
Two tables, one JOIN and suddenly revenue doubles. Unfortunately, only in the report. Here is how I choose a join for the question and check that the result still represents the right things.
Advanced SQL queries: CTEs, windows and rankings in one example
A query with three CTEs can look impressive while calculating the wrong sales total. I build one analysis step by step, keeping track of row meaning, missing values and the order of calculations.
SQL pivot tables: CASE WHEN, COUNT and the traps around NULL
A pivot can look correct while losing orders. I show how to count categories, distinguish zero from missing data and reconcile the columns with the source.
