SQL Arena Blog
SQL tutorials
Basics: SELECT and filtering
4 articlesWhat is SELECT … FROM in SQL? Reading from a table for beginners
SELECT is the heart of SQL. Every query starts here. We cover the syntax, picking specific columns, aliases, computed fields, common mistakes, and three practical exercises.
What is WHERE in SQL? Row filtering for beginners
WHERE is the row filter in SQL. Plain English with lots of examples: =, IN, BETWEEN, LIKE, IS NULL and AND/OR. Tables before/after, beginner pitfalls, and a quick practice at the end.
What is LIMIT in SQL? Capping the number of rows for beginners
LIMIT caps the query result at N rows. Essential for pagination, top-N queries, and previews. We cover the syntax, OFFSET, PostgreSQL vs MySQL differences, common mistakes, and three practice exercises.
What is ORDER BY in SQL? Sorting query results for beginners
ORDER BY sorts the result of a SQL query. We cover ASC and DESC, multi-column sorting, NULLs in sort order, and the classic ORDER BY + LIMIT for top-N. Plenty of examples and three exercises.
Joining tables (JOIN)
7 articlesWhat is INNER JOIN in SQL? Joining tables for beginners
INNER JOIN combines rows from two tables by a shared key. The simplest and most common JOIN. We cover the syntax, ON clause, multi-table joins, common mistakes, and three practice exercises.
What is LEFT JOIN in SQL? A beginner's guide
LEFT JOIN keeps every row from the left table; missing matches on the right become NULL. We cover the syntax, the difference from INNER JOIN, finding orphans, and three exercises.
Aliases (AS) in SQL: table and column aliases for beginners
Aliases are short names for tables and columns in SQL. They make queries readable and become mandatory when JOINing tables with shared column names. Syntax, must-have cases, common mistakes, and three exercises.
SQL FULL OUTER JOIN: Reconciling Data and Faking It in MySQL
A practical look at FULL OUTER JOIN: getting every row from both sides, where NULLs appear, why it's perfect for reconciliation, and how to emulate it in MySQL.
SQL CROSS JOIN: Cartesian Products, Generating Combinations and Calendars
A practical look at CROSS JOIN: Cartesian products, generating every combination and gap-free calendars, plus how to catch accidental cross joins.
SQL Self-Joins: Joining a Table to Itself
Use table aliases to join a table to itself: employee→manager hierarchies, comparing rows within one table, and generating pairs without duplicates.
SQL Anti-Joins: Finding Rows With No Match
Three ways to find rows with no match — LEFT JOIN / IS NULL, NOT EXISTS and NOT IN — and why NOT IN breaks on NULL.
Aggregation and grouping
25 articlesWhat are COUNT, SUM, AVG, MIN, MAX in SQL? Aggregate functions for beginners
Aggregate functions are tools for "computing something across a group of rows". COUNT — how many rows, SUM — total, AVG — average, MIN/MAX — smallest and largest. Plain words: the difference between COUNT(*) and COUNT(column), how NULL affects aggregates, common scenarios, and pitfalls.
What is GROUP BY in SQL? Grouping rows for beginners
GROUP BY is the SQL command for "collapse rows into groups and aggregate". Plain words: how to get "how many orders each customer has" in a single query. What you can and can't put in SELECT, the difference from DISTINCT, GROUP BY on multiple columns, and common pitfalls.
What is HAVING in SQL? Filtering groups for beginners
HAVING is the filter that runs after GROUP BY and applies to aggregates. Plain words: WHERE filters input rows, HAVING filters output groups. We'll cover the difference, typical patterns (top-N, anomalies), and why WHERE and HAVING get confused.
STRING_AGG in SQL: Concatenate Grouped Rows with a Delimiter and ORDER BY
How to roll many rows into one delimited string in the right order — using PostgreSQL STRING_AGG, MySQL GROUP_CONCAT, and ClickHouse arrayStringConcat(groupArray()).
COUNT(*) FILTER (WHERE ...): Conditional Aggregates in One Pass
How the FILTER clause computes several segmented metrics in a single pass and replaces clunky CASE-inside-aggregate.
ARRAY_AGG in PostgreSQL: Collect Grouped Values into an Array with ORDER BY and FILTER
Fold a group's rows into an ordered array with ARRAY_AGG, filter and dedupe it, unnest it back to rows, and learn the MySQL workaround.
SQL GROUPING SETS: Subtotals and Grand Totals in One Query
Use GROUPING SETS to compute several grouping levels in a single pass and tell subtotal rows apart from real NULLs.
SQL ROLLUP: Hierarchical Subtotals and a Grand Total in One Query
How GROUP BY ROLLUP adds subtotals and a grand-total row to ordinary aggregation, and how to read the trailing NULL rows with GROUPING().
PERCENTILE_CONT in PostgreSQL: Median and Percentiles with WITHIN GROUP
Compute the median and p95 with a single PERCENTILE_CONT aggregate, interpolation included, compare it to PERCENTILE_DISC, and see why it beats AVG.
UNNEST in PostgreSQL: Expand an Array into Rows, WITH ORDINALITY and ARRAY_AGG
Expand an array into rows with UNNEST, get an index via WITH ORDINALITY, unnest several arrays in parallel, and fold everything back with ARRAY_AGG.
COUNT(DISTINCT) in SQL: Counting Unique Values and Its Cost
How to count unique values with COUNT(DISTINCT), why it gets expensive at scale, and when to reach for approximate HLL alternatives.
BOOL_AND and BOOL_OR in PostgreSQL: Boolean Aggregates per Group
Check "every row in the group is true" with BOOL_AND and "at least one" with BOOL_OR, see how NULLs behave, what EVERY adds, and how to emulate it in MySQL and ClickHouse.
EVERY in SQL: Asserting a Condition Holds for the Whole Group
EVERY is the SQL-standard synonym for BOOL_AND: true when a condition holds for every counted row, NULL over an empty group.
SQL STDDEV: STDDEV_SAMP vs STDDEV_POP and Outlier Detection
How STDDEV_SAMP differs from STDDEV_POP, why bare STDDEV is the sample version, computing mean +/- sd, and flagging outliers.
SQL VARIANCE: VAR_SAMP vs VAR_POP and the Link to STDDEV
How VAR_SAMP and VAR_POP work in PostgreSQL, why bare VARIANCE equals VAR_SAMP, and how variance ties to standard deviation.
MODE() WITHIN GROUP in SQL: the Most Frequent Value in One Expression
How to find the most frequent value with MODE() WITHIN GROUP, how ties break, and why it beats the GROUP BY + COUNT + LIMIT 1 trick.
PERCENTILE_DISC in SQL: Discrete Percentiles Without Interpolation
How PERCENTILE_DISC returns an actual value from your data without interpolation, how it differs from PERCENTILE_CONT, and when to pick the discrete variant.
BIT_OR and BIT_AND in PostgreSQL: Bitwise Aggregates over Flag Masks
How BIT_OR collects every set bit in a group, BIT_AND finds bits common to all rows, and how to read the result with & for permission and feature-flag masks.
JSON_AGG and JSONB_AGG in PostgreSQL: Build a JSON Array for an API Response
Fold rows into a JSON array with JSON_AGG and JSONB_AGG, order the aggregate, build a nested API response in one query, and skip NULLs cleanly.
JSONB_OBJECT_AGG in PostgreSQL: Fold Key/Value Rows into a Single JSON Object
Fold key/value rows into a single JSON object with JSONB_OBJECT_AGG, learn the duplicate-key behavior, build a lookup document per group, and see how it differs from JSONB_AGG.
CORR in SQL: Pearson Correlation as a Single Aggregate
How CORR(y, x) computes Pearson correlation, what its sign and magnitude mean, how NULL pairs behave, and how to add a trend line with REGR_*.
REGR_SLOPE and REGR_INTERCEPT in PostgreSQL: A Trend Line and Forecast in One Query
Build a least-squares trend line right in SQL with REGR_SLOPE and REGR_INTERCEPT, watch the (y, x) argument order, check REGR_COUNT, and forecast without an external stats package.
REGR_R2 in SQL: R-squared and Goodness of Fit for Regression
How REGR_R2 measures the quality of a linear regression in SQL and why you should always read it next to REGR_SLOPE.
Pivot Rows into Columns with MAX/SUM FILTER in SQL
Turn a tall table into a wide cross-tab in a single GROUP BY using aggregates with the FILTER clause.
SQL CUBE: Every Grouping Combination in One Pass for Cross-Tab Reports
How GROUP BY CUBE computes every column combination at once, per dimension and grand total, and how to read NULL subtotals with GROUPING() and tell CUBE from ROLLUP.
Subqueries and DISTINCT
4 articlesWhat is DISTINCT in SQL? Unique values for beginners
DISTINCT means "remove duplicates". Plain words: unique values of a column or combination of columns, the difference from GROUP BY, NULL handling, and the PostgreSQL-specific DISTINCT ON for "one row per group". With before/after tables and common pitfalls.
What is IN with a subquery in SQL? Membership check for beginners
IN with a subquery filters "rows where a column's value appears in another query's result". Plain words: filtering by a dynamic list, the difference from a literal list, the NOT IN-with-NULL trap, and comparison with EXISTS. With tables and common pitfalls.
What is EXISTS in SQL? Existence check for beginners
EXISTS asks "is there at least one row matching the condition?". Plain words: a filter on the presence of a related record (e.g. "customers with at least one order"), the difference from IN with a subquery, NOT EXISTS, and NULL behavior.
What is a scalar subquery in SQL? Single value in SELECT for beginners
A scalar subquery is a SELECT that returns exactly one value and slots into a column position or WHERE expression. Plain words: pull one field from a related table, add a summary number to each row of a report, use as a constant in a condition. With tables and common pitfalls.
Window functions
10 articlesWhat is ROW_NUMBER in SQL? Row numbering for beginners
ROW_NUMBER assigns "a sequential number to each row". Plain words: the first window function worth learning. Descending numbering, numbering within groups via PARTITION BY, the classic top-N per group pattern, and dedup. With tables and common pitfalls.
What are RANK and DENSE_RANK in SQL? Ranking with ties for beginners
RANK and DENSE_RANK are ranking functions where equal values get equal ranks. Plain words: the difference between ROW_NUMBER (always unique), RANK (ties get equal rank with gaps after), and DENSE_RANK (equal rank without gaps). With tables, an Olympic-style example, and common pitfalls.
What is PARTITION BY in SQL? Groups inside a window for beginners
PARTITION BY is the part of OVER that splits rows into groups for window functions. Plain words: like GROUP BY, but rows don't collapse — each row stays, with its group's aggregate appended. The difference from GROUP BY, typical patterns, and aggregate behavior inside windows.
What are LAG and LEAD in SQL? Neighbouring rows in a window for beginners
LAG and LEAD return the value from the **previous** or **next** row of a window. Plain words: day-over-day delta, time-to-next-event, price change — tasks that previously required self-joining a table. With tables and common pitfalls.
SQL Window Frames: ROWS/RANGE, Running Totals, and the LAST_VALUE Trap
Understand how ROWS and RANGE BETWEEN frames work, build running totals and moving averages, and avoid the classic LAST_VALUE default-frame trap.
Window functions in SQL: ROW_NUMBER, RANK, LAG/LEAD in practice
Window functions are the analyst's most-used tool in SQL. We'll break down ROW_NUMBER, RANK, LAG/LEAD and PARTITION BY through real cases: top-N per group, day-over-day metrics, cumulative sums.
SQL NTILE: Quartiles, Deciles and Cohorts in Equal Buckets
How NTILE(n) splits ordered rows into n nearly equal buckets, where the remainder lands, and how it differs from WIDTH_BUCKET.
FIRST_VALUE and LAST_VALUE in PostgreSQL: First and Last per Partition and the Frame Trap
Pull the first and last value of a partition with FIRST_VALUE and LAST_VALUE, and learn why LAST_VALUE returns the current row until you widen the window frame.
PERCENT_RANK and CUME_DIST: Leaderboard Percentiles in SQL
How to compute a row's relative standing in 0..1 with PERCENT_RANK and CUME_DIST, and how to make the result deterministic.
NTH_VALUE in PostgreSQL: the n-th Window Value and Second-Highest per Group
Grab the n-th value inside a window frame with NTH_VALUE, widen the frame like LAST_VALUE, learn where FROM LAST and IGNORE NULLS really work, and compute the second-highest per group.
CTEs (WITH)
5 articlesWhat is WITH … AS (CTE) in SQL? Common Table Expressions for beginners
WITH … AS is a "named intermediate result", aka CTE (Common Table Expression). Plain words: a way to break complex queries into readable steps, reuse computed values, and write SQL you'll actually want to re-read later. With tables and common pitfalls.
Multiple CTEs in one query: chained WITH for beginners
Multiple CTEs separated by commas let you build a query as a chain of steps: compute one thing, build the next on top of it, then assemble the final result. Plain words: chained CTEs, reusing intermediate results, and recursive CTEs for hierarchies.
Recursive CTEs in SQL: WITH RECURSIVE for Trees, Graphs and Number Series
How WITH RECURSIVE works: an anchor plus a recursive step joined by UNION ALL, walking org charts and graphs, generating number series, and staying safe from infinite loops.
LATERAL JOIN in PostgreSQL: Top-N per Group and Correlated FROM Subqueries
How LATERAL lets a subquery reference columns of earlier FROM items, solves top-N per group, and why LEFT JOIN LATERAL ... ON true keeps outer rows.
SQL generate_series: Calendars, Number Ranges and Filling Gaps with Zeros
How generate_series builds a continuous run of integers, dates and timestamps, and why it is the cleanest way to get a gap-free axis.
Data changes (DML)
10 articlesWhat is INSERT in SQL? Adding rows for beginners
INSERT is the SQL command for "add a new row". Plain words: basic syntax, batch inserts of multiple rows in one shot, INSERT FROM SELECT, RETURNING to get back generated IDs, and UPSERT via ON CONFLICT for idempotent operations. With before/after tables and common pitfalls.
What is UPDATE in SQL? Modifying rows for beginners
UPDATE is the SQL command for "change data in existing rows". Plain words: what to change, why WHERE is non-negotiable, how to update several columns at once. Before/after tables, beginner pitfalls, a quick recap, and three practice tasks at the end.
What is DELETE in SQL? Removing rows for beginners
DELETE is the SQL command for "remove rows from the table". Plain words: what to delete, why WHERE is non-negotiable, soft-delete vs hard-delete, the difference from TRUNCATE, and ON DELETE CASCADE. Before/after tables, common mistakes, a quick recap, and three practice tasks.
UPSERT in PostgreSQL: INSERT ... ON CONFLICT in Practice
How to insert-or-update in a single statement with INSERT ... ON CONFLICT, use EXCLUDED, write idempotent inserts, and build atomic counters.
MERGE in PostgreSQL 15+: MATCHED / NOT MATCHED, Upserts, and Table Sync
A practical look at MERGE in PostgreSQL 15+: the MATCHED and NOT MATCHED branches, upsert and sync patterns, and when to reach for it instead of ON CONFLICT.
INSERT ... ON CONFLICT DO NOTHING in PostgreSQL: Idempotent Inserts Without Errors
How ON CONFLICT DO NOTHING makes INSERT idempotent, how a conflict target differs from a constraint name, why RETURNING stays silent on skipped rows, and how to seed data in bulk.
RETURNING in PostgreSQL: Get IDs and Changed Rows Without a Second Round-Trip
How the RETURNING clause on INSERT/UPDATE/DELETE hands back generated ids and changed columns in one round-trip, with no extra SELECT.
DELETE ... USING in SQL: JOIN-Style Deletes Without a Subquery
How DELETE ... USING filters rows against another table via a join, why it beats a WHERE IN subquery, and the MySQL multi-table DELETE form.
UPDATE ... FROM: Bulk Updates Driven by Joins and Subqueries
Update a table from another table or an aggregate, and avoid the missing-WHERE trap that overwrites every row.
CTE + DELETE ... RETURNING: Move Rows in One Statement
Archive and move rows atomically with data-modifying CTEs, with no race window between DELETE and INSERT.
Schema (DDL)
10 articlesWhat is CREATE TABLE in SQL? Creating a table for beginners
CREATE TABLE is the SQL command for "create a new table". Plain words: which columns and what types, what NOT NULL, DEFAULT, PRIMARY KEY, and FOREIGN KEY do, and why thinking about the schema upfront pays off. With before/after tables, common beginner mistakes, and three exercises.
What is ALTER TABLE in SQL? Changing table structure for beginners
ALTER TABLE is the SQL command for "change the structure of an existing table". Plain words: add a column, drop, rename, change a type, add and drop constraints. Plus the main pain on production — long table locks and the add → backfill → drop pattern for safe migrations.
SQL CHECK Constraints: Enforcing Invariants in the Database
Use CHECK to lock business rules into the schema: positive amounts, allowed statuses, date ranges, and multi-column validation.
Foreign Key ON DELETE: CASCADE, SET NULL and RESTRICT in Practice
What happens to child rows when you delete a parent, and how to choose between CASCADE, SET NULL, RESTRICT and NO ACTION without losing data.
NOT VALID + VALIDATE: Add Constraints Without Long Locks
Add a foreign key or CHECK to a large production table in two steps, without halting writes.
Generated Columns in PostgreSQL: GENERATED ALWAYS AS STORED
How to keep a derived value in one place with GENERATED ALWAYS AS (...) STORED, index it, and choose it over a trigger or a view.
Partial UNIQUE Indexes: Uniqueness Over a Subset of Rows
Enforce uniqueness over only some rows: one active record per key, re-registration after soft-delete, and a single default per group.
Range Partitioning in PostgreSQL: Time-Series Tables Done Right
Split a huge table by date range, drop old data in milliseconds, and let the planner read only the partitions a query needs.
SQL Triggers: BEFORE/AFTER, NEW/OLD, and the updated_at Pattern
How BEFORE/AFTER and ROW/STATEMENT triggers work, what the function returns, the auto-updated_at pattern, audit logging, and when triggers hurt.
SQL Materialized Views: Caching Expensive Query Results
How a materialized view caches the result of a heavy query, how REFRESH works, and when a plain view or summary table fits better.
Strings and dates
41 articlesWhat are LOWER, UPPER, LENGTH in SQL? String functions for beginners
String functions are a daily SQL tool: case normalization (LOWER, UPPER), length (LENGTH), whitespace trimming (TRIM), substrings (SUBSTRING), replace (REPLACE). Plain words: case-insensitive search, data cleanup, and Unicode gotchas.
What is CONCAT in SQL? String concatenation for beginners
CONCAT means "join strings together". Plain words: three forms (||, CONCAT, CONCAT_WS), how each treats NULL differently, and why CONCAT_WS is the best choice for addresses and names. With tables and common pitfalls.
What is EXTRACT in SQL? Year, month, day from a date for beginners
EXTRACT pulls a piece out of a date: year, month, day, hour, day of week. Plain words: how to group by year/month, filter by weekday, count seconds via EPOCH. Compared with DATE_TRUNC and PostgreSQL/MySQL differences.
SQL DATE_TRUNC: Rounding Timestamps Down to Buckets for Time Series
How DATE_TRUNC floors a timestamp to an hour, day, week, month or year, and why it is the default tool for bucketing time series.
NOW, CURRENT_DATE and INTERVAL Arithmetic in SQL
How to filter rows for the last 7 days and the current month using NOW(), CURRENT_DATE and INTERVAL math across PostgreSQL, MySQL and ClickHouse.
Type Conversion in SQL: CAST and the :: Operator in PostgreSQL
How to convert types in SQL with CAST and the :: shorthand, round numeric safely, guard against conversion errors, and handle PostgreSQL, MySQL and ClickHouse differences.
TRIM, SUBSTRING and REPLACE: Cleaning Up Strings in SQL
How to trim whitespace, slice substrings and swap characters to build normalized keys and strip formatting.
SPLIT_PART in PostgreSQL: Split a String and Take the N-th Field
Pull the domain from an email, a segment from a path, or a code from a SKU with a single SPLIT_PART call, plus MySQL and ClickHouse equivalents.
ILIKE in PostgreSQL: Case-Insensitive Pattern Matching
How ILIKE works in PostgreSQL, why it beats lower(col) LIKE lower(...), and how to make it fast with pg_trgm indexes.
LEFT and RIGHT in SQL: First and Last Characters of a String in PostgreSQL
How LEFT and RIGHT grab the first or last N characters of a string in PostgreSQL: masking cards, prefixes, negative length, and how they compare to SUBSTRING.
POSITION and STRPOS in SQL: Finding a Substring's Index
How to find a substring's position in SQL with POSITION and STRPOS, why results start at 1, what 0 means, and how to split strings with SUBSTRING.
LPAD and RPAD in SQL: Padding Strings to a Fixed Width
How to use LPAD and RPAD to pad strings to a fixed width, zero-pad ids and invoice numbers, and build fixed-width columns for exports.
INITCAP in PostgreSQL: Title-Casing Strings the Right Way
How INITCAP capitalizes the first letter of every word in PostgreSQL, where it breaks on apostrophes and hyphens, and how to emulate it in MySQL.
SQL REPEAT: Repeat Strings for Separators, Placeholders and Text Bar Charts
How REPEAT(str, n) repeats a string n times and why it is a handy tool for separators, placeholders and text bar charts right inside SQL.
REVERSE in PostgreSQL: Flip Strings, Suffix Lookups, and Palindrome Checks
How REVERSE flips a string character by character, powers suffix lookups via an index, checks palindromes, reverses delimited lists, and where multibyte bites.
SQL char_length: Counting Characters, Not Bytes
char_length returns the number of characters in a string, octet_length its size in bytes, and LENGTH counts characters or bytes depending on PostgreSQL, MySQL, or ClickHouse.
SQL REGEXP_REPLACE: Pattern-Based String Cleaning with the g and i Flags
How REGEXP_REPLACE swaps substrings by pattern, what the g and i flags do, how back-references work, and where POSIX and PCRE diverge.
REGEXP_MATCHES in PostgreSQL: Capture Groups and the g Flag
How REGEXP_MATCHES returns capture groups as an array, what the g flag really does, and why no match drops the row.
REGEXP_SPLIT_TO_ARRAY: Splitting Strings by a Regex Delimiter
How to split strings on a regex into an array or rows, and why it beats SPLIT_PART for messy input.
SQL TRANSLATE: Per-Character Mapping, Deletion and Transliteration
How TRANSLATE maps one character set onto another, deletes leftover characters, and why it is fundamentally different from REPLACE.
BTRIM, LTRIM and RTRIM in SQL: Trimming Spaces and Any Characters
How to strip spaces and arbitrary characters from the edges of a string using BTRIM, LTRIM and RTRIM, and how they relate to standard TRIM.
The SQL FORMAT Function: String Templates with %s, %I and %L in PostgreSQL
How to build strings from a template with PostgreSQL FORMAT: the %s, %I and %L specifiers, safe dynamic SQL, and why it beats concatenation.
STARTS_WITH in PostgreSQL: A Readable Prefix Test Instead of LIKE
Why STARTS_WITH reads better than LIKE 'x%', how it handles case and indexes, and how to emulate it in MySQL and ClickHouse.
SQL ascii and chr: Code Points and Characters
ascii() returns the code point of the first character and chr() builds a character from a code; in PostgreSQL both handle full Unicode, while MySQL and ClickHouse differ.
to_hex in PostgreSQL: Convert an Integer to a Hexadecimal String
How to_hex(int) turns a number into a hex string for colors, bit masks and flag debugging, how to reverse it, and how MySQL and ClickHouse differ.
SQL AGE: Date Differences as Calendar-Aware Intervals
PostgreSQL's AGE returns the gap between two timestamps as years, months and days instead of raw days.
SQL DATE_PART: Numeric Date Fields, Day of Week, and Dynamic Field by String
How DATE_PART returns a numeric date field, how it differs from EXTRACT, and why dow numbers Sunday as 0.
SQL EXTRACT(EPOCH FROM ...): Durations in Seconds and Unix Time
How EXTRACT(EPOCH FROM ...) turns an interval into total seconds and a timestamp into Unix time, why those two cases differ, and how to round-trip back with to_timestamp.
SQL TO_CHAR: Formatting Dates and Numbers into Strings with Templates
How TO_CHAR turns a date or number into a string with a template: YYYY-MM-DD, HH24:MI, month names, thousands separators, and how it differs from MySQL.
TO_DATE in PostgreSQL: parsing a string into a date by template
How to parse a string into a date with TO_DATE and an explicit template, why it beats a ::date cast, how range checks changed in PostgreSQL 16, and the two-digit-year gotcha.
SQL TO_TIMESTAMP: Parsing Strings and Building Timestamps from Unix Epoch
Two modes of TO_TIMESTAMP: parse a string by template into timestamptz or build a moment from Unix epoch seconds, with all the time-zone nuances.
CURRENT_TIMESTAMP vs LOCALTIMESTAMP in SQL
How CURRENT_TIMESTAMP differs from LOCALTIMESTAMP, why both freeze inside a transaction, and when to reach for clock_timestamp().
SQL CURRENT_DATE and CURRENT_TIME: Today's Date and Time of Day
CURRENT_DATE and CURRENT_TIME are parenthesis-free SQL specials: today's date and the time of day with its zone.
SQL Date Arithmetic: Adding Days, Subtracting Dates, and Intervals
How to add integers to dates, subtract dates into day counts, and add intervals to timestamps in PostgreSQL, MySQL and ClickHouse.
SQL make_date and make_time: Build Dates and Times from Integer Parts
How to build a date or time from separate integer report columns without format strings, and why make_date validates out-of-range parts.
make_timestamp and make_interval in SQL: Build Dates and Intervals from Parts
make_timestamp assembles a timestamp from numeric parts and make_interval builds an interval from named arguments instead of fragile string concatenation.
SQL AT TIME ZONE: Converting Stored UTC to a User's Local Time
The dual behavior of AT TIME ZONE: on timestamptz it returns local wall time, on a naive timestamp it interprets the value as being in that zone and returns timestamptz.
JUSTIFY_INTERVAL in PostgreSQL: Making Durations Human-Readable
JUSTIFY_HOURS, JUSTIFY_DAYS and JUSTIFY_INTERVAL roll a raw interval into a clean days-and-months shape.
DATE_BIN in PostgreSQL: Arbitrary-Width Time Buckets for Metrics
How PostgreSQL 14's DATE_BIN floors a timestamp to the start of an arbitrary-width bucket from a chosen origin, and where it beats DATE_TRUNC.
The SQL OVERLAPS Operator: Detecting Period Intersections and Conflicts
How to use OVERLAPS to test whether two time periods intersect and to find booking and shift conflicts in PostgreSQL.
timestamptz vs timestamp in SQL: storing time correctly
Why events should almost always be timestamptz, how it differs from naive timestamp, and how to avoid silently dropping the zone when you mix them.
CASE and NULL
4 articlesWhat is CASE WHEN in SQL? Conditional logic for beginners
CASE WHEN is "if/else inside SQL". Plain words: how to put conditions right into SELECT, the difference between searched CASE and simple CASE, bucketing numbers into categories, pivoting with one formula, and conditional aggregates. With tables and common pitfalls.
What is COALESCE in SQL? Handling NULL for beginners
COALESCE returns the first non-NULL value from a list. Plain words: defaults for missing data, fallback chains (nickname → name → 'Guest'), safe arithmetic, and pairing with NULLIF. With tables and common pitfalls.
What is NULLIF in SQL? Safe division and cleanup for beginners
NULLIF returns NULL if two values are equal. Plain words: safe division via NULLIF(x, 0), cleaning placeholder values like '' or 'unknown', and pairing with COALESCE for clean data handling. With tables and common pitfalls.
NULL and IS DISTINCT FROM: NULL-Safe Equality in SQL
Why = NULL is never true and how IS DISTINCT FROM gives you NULL-safe equality for change detection and dedup.
Other
53 articlesThe JSONB @> Operator in PostgreSQL: Document Containment and GIN-Index Acceleration
How the @> operator checks whether a JSONB document contains a fragment, matches nested objects and array members, rides a GIN index, and differs from ->> filters.
SQL ROUND(x, n): Rounding to Decimal Places, Negative n, and Currency Formatting
How ROUND(x, n) rounds to n decimal places, what a negative n does, and why PostgreSQL needs numeric (not double precision) for the two-argument form.
Indexing JSONB with GIN: jsonb_ops vs jsonb_path_ops
How to speed up JSONB filters in PostgreSQL with GIN indexes, and when to pick jsonb_path_ops over the default operator class.
Composite Indexes in SQL: Leftmost-Prefix Rule and Column Order
How a composite index works under the leftmost-prefix rule, how to order columns for filter, sort and range, and when it beats two single-column indexes.
Sargable WHERE: Writing Index-Friendly Predicates
Why wrapping a column in a function kills the index and how to rewrite WHERE into ranges so PostgreSQL actually uses it.
SQL ROUND: Rounding to the Nearest Integer, Banker's Rounding and Money
How ROUND rounds to the nearest integer, why half-away-from-zero differs from banker's rounding, and why numeric beats double for money.
CEIL / CEILING in SQL: Rounding Up to the Next Integer
How CEIL/CEILING rounds up, computes pagination page counts and buckets, and how it differs from FLOOR and ROUND.
Deleting from JSONB in PostgreSQL: the - and #- operators
How the - and #- operators drop keys, array elements and nested values from a JSONB document.
JSONB_BUILD_OBJECT in PostgreSQL: Build a JSON Object from Columns with Typed Values
Build a JSON object from alternating key/value pairs with preserved types, shape per-row API payloads, nest objects and arrays, and see how it differs from to_jsonb.
jsonb_build_array in PostgreSQL: Build JSON Arrays from Mixed-Type Values
How to assemble JSON arrays from arguments of any type, nest them with jsonb_build_object, and avoid confusing it with to_jsonb of a SQL array.
JSONB Arrow Operators: -> and ->> in PostgreSQL
How -> and ->> work: read object fields and array elements, drill into nested structures by chaining, and cast the result to the right type.
SQL ABS: Absolute Value, Deltas, and Tolerance Checks
How ABS computes magnitude, measures the delta between two values, powers tolerance checks in WHERE, and pairs with SIGN.
jsonb_array_length in PostgreSQL: Counting JSON Array Elements Safely
How jsonb_array_length counts JSON array elements, why it raises the non-array error, how jsonb_typeof guards it, and how to filter rows by array size.
jsonb_array_elements: Expand a JSON Array into Rows in PostgreSQL
How to expand a JSON array into one row per element, filter and join on items, get the index with WITH ORDINALITY, and when to reach for the _text variant.
SQL EXP and LN: the Exponential, the Natural Log, and Geometric Means
How EXP and LN compute e^x and the natural log, why the log/exp pair powers geometric means and growth rates, and why LN(0) raises an error.
JSONB_AGG in PostgreSQL: Collect Grouped Rows into a JSON Array for JSON APIs
Fold a group's rows into a JSON array with ORDER BY and FILTER, build a nested document via a correlated subquery, and coalesce an empty group to '[]'.
SQL FLOOR: Rounding Down to the Previous Integer and Integer Bucketing
How FLOOR rounds a number down to the previous integer, why negatives move further from zero, and how it differs from TRUNC.
UNION vs UNION ALL in SQL: Combining Query Results
How UNION and UNION ALL differ, the column compatibility rules you must follow, and how to sort the combined result.
GREATEST and LEAST in SQL: Row-Wise Max and Min Across Columns
How GREATEST and LEAST return the max and min within a single row, clamp a value to a range, and differ in NULL handling across PostgreSQL, MySQL, and ClickHouse.
Integer Division in SQL: DIV, MOD, and the Cast Trap
How integer division works in PostgreSQL and MySQL, when to reach for DIV, and how to pair it with MOD to get quotient and remainder together.
SQL INTERSECT: Rows Present in Both Queries
How the INTERSECT operator finds rows common to two queries, how it differs from INTERSECT ALL, and when a JOIN or EXISTS fits better.
EXCEPT in SQL: Set Difference and the Oracle MINUS Equivalent
How EXCEPT returns rows from the first query that are absent in the second, how it differs from EXCEPT ALL, and when NOT EXISTS wins.
Extracting JSONB as Text: the ->> Operator in PostgreSQL
How to pull a scalar field out of JSONB as text with ->>, how it differs from ->, how to cast the result, and how to filter on JSON fields.
CREATE INDEX CONCURRENTLY: zero-downtime indexing in PostgreSQL
How to build an index on a hot table without a heavy write lock, and how to clean up the INVALID index a failed build leaves behind.
Reading EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL: Actual vs Estimated
How to read EXPLAIN (ANALYZE, BUFFERS): actual vs estimated rows and time, loops, the estimate-to-actual gap that signals stale stats or a missing index.
SELECT ... FOR UPDATE: Pessimistic Row Locking in SQL
How to lock rows with SELECT ... FOR UPDATE, avoid deadlocks, and build a correct money-transfer pattern.
Conditional UPDATE in SQL: Atomic Check-and-Set with WHERE
Encode a precondition inside one UPDATE's WHERE, detect failure by the affected-row count, and avoid lost updates under concurrency: compare-and-swap in SQL.
Atomic Counter: UPDATE SET n = n + 1 Without Lost Updates
Why read-modify-write loses increments under load, and how a single UPDATE SET n = n + 1 keeps a counter correct under concurrency.
SQL GRANT: Privileges on Tables, Schemas and Columns with Least Privilege
How to grant privileges on tables, schemas and columns, the difference between object privileges and role membership, and how to design access with least privilege.
SQL REVOKE: Safely Taking Back Granted Privileges
How to use REVOKE to take back granted privileges, handle CASCADE and RESTRICT, and not get caught out by PUBLIC or role-based access.
CREATE ROLE in PostgreSQL: Users, Group Roles and Inheritance
How a single CREATE ROLE statement models both people and groups: LOGIN vs NOLOGIN, role membership, inheritance and attributes like CREATEDB.
Read-only Role in PostgreSQL: Correct Grants for Analytics and BI
How to build a read-only role for analysts: USAGE on the schema, SELECT on tables, and ALTER DEFAULT PRIVILEGES so new tables stay readable.
WIDTH_BUCKET in SQL: Equal-Width Histogram Buckets and Distributions
How WIDTH_BUCKET maps values into equal-width ranges, handles out-of-range underflow/overflow, and powers distributions with GROUP BY.
SQL TRUNC: Dropping the Fractional Part Toward Zero Without Rounding
How TRUNC chops the fractional part toward zero, how TRUNC(x, n) cuts to a number of decimals, and why it differs from FLOOR on negatives.
to_jsonb in PostgreSQL: Turn a Whole Row into a JSON Object
How to_jsonb converts any value, row or array into jsonb while preserving types, and how it differs from json_build_object and row_to_json.
SQRT in SQL: Square Roots, Euclidean Distance, and Standard Deviation
How SQRT behaves in PostgreSQL, why negative input throws, and how to compute Euclidean distance and standard deviation by hand.
SIGN in SQL: the Sign of a Number and Branching by Direction
How SIGN returns -1/0/1, how to branch on the direction of change, and why you pair SIGN with ABS.
The POWER Function in SQL: Exponents, Roots, and Compound Growth
How to use POWER for exponentiation, fractional roots, and compound-growth math in PostgreSQL, MySQL, and ClickHouse.
MOD and the % Operator in SQL: Remainders in Practice
How MOD and the % operator compute remainders: even/odd, every N-th row, sharding by id, and the sign rule for negatives.
SQL LOG: Base-10 and Arbitrary-Base Logarithms, and the MySQL Natural-Log Trap
How LOG(x) base 10 and LOG(b, x) for an arbitrary base work in PostgreSQL, why MySQL LOG is a trap, and where logarithms help in practice.
JSONB_TYPEOF in PostgreSQL: Guarding Dynamic JSON Safely
How jsonb_typeof returns a JSON value's type as text and protects jsonb_array_length, arithmetic, and validation of semi-structured input.
JSONB_SET in PostgreSQL: Patch a Single Field Inside a JSON Document
Replace a value at a path in JSONB, add a missing key with create_missing, patch a nested field with UPDATE, and drop keys with the minus operator.
jsonb_pretty in PostgreSQL: Readable JSONB Output
How to format JSONB with indentation using jsonb_pretty for debugging in psql, and when not to.
JSONB Path Operators in PostgreSQL: #> and #>> for Nested Values
How to read deeply nested JSONB values in PostgreSQL with the #> and #>> path operators, mix keys and indexes, and when to reach for jsonb_path_query.
JSONB_OBJECT_KEYS in PostgreSQL: List a JSON Object's Top-Level Keys as Rows
Expand a JSONB object's top-level keys into rows, discover the shape of your data, check which fields exist, and collect keys with array_agg.
JSONB Key-Existence Operators in PostgreSQL: ?, ?| and ?&
How to check for keys in JSONB with ?, ?| and ?&, speed it up with a GIN index, and avoid the placeholder quoting trap.
JSONB_EACH in PostgreSQL: Expand a JSON Object into Key/Value Rows
Use jsonb_each to expand a JSON object into (key, value) rows, iterate over dynamic keys, filter and aggregate entries, and know when to reach for the _text variant.
The JSONB || Operator in PostgreSQL: Merging Documents and Patches
How the || operator does a shallow JSONB merge where right-hand keys win, appends to a JSON array, and applies a partial patch in one expression.
RANDOM() in SQL: Random Sampling, Integers, and Seeding
How RANDOM() works in PostgreSQL, why ORDER BY RANDOM() is slow at scale, and what to use instead.
NOT EXISTS vs NOT IN: the NULL Trap and Anti-Joins
Why NOT IN silently returns nothing when the subquery has a NULL, and when to pick NOT EXISTS or LEFT JOIN ... IS NULL.
Partial Indexes in PostgreSQL: Indexing Only the Hot Rows
How CREATE INDEX ... WHERE lets you cover only the active rows for a smaller, faster index, plus a partial UNIQUE that plays nicely with soft deletes.
Reading Query Plans: EXPLAIN and EXPLAIN (ANALYZE, BUFFERS)
A hands-on guide to PostgreSQL query plans: Seq Scan vs Index Scan, estimated vs actual rows, and how to catch a missing index.
Job Queues in SQL: SELECT ... FOR UPDATE SKIP LOCKED
How to make a dozen workers safely pull tasks from a queue table without blocking each other, using FOR UPDATE SKIP LOCKED in PostgreSQL.