Pages

Topics

Tools

↑↓navigate↵openescclose

SQL guides

Every published SQL guide, newest first, with practical examples you can copy into your own project.

3,815 guides across 14 topics

Topics

Latest SQL guides

3D illustration of missing NULL voids intercepted by COALESCE fallback bridges with a ternary truth table TRUE FALSE UNKNOWN
SQL

SQL NULL Handling: COALESCE, NULLIF, IS NULL, and Three-Valued Logic

Why every comparison with NULL is UNKNOWN, why NOT IN can return nothing, and how unique indexes, sorting and NOT NULL migrations behave with NULL.

SQLType SystemBest Practices
3D illustration of incoming data rows updating existing duplicate keys or inserting fresh records without collision errors
SQL

SQL UPSERT: INSERT ON CONFLICT, MERGE Statements, and Duplication Handling

The dialect split between ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE, why DO NOTHING breaks RETURNING, and the deadlocks bulk upserts cause.

SQLData ManipulationDML
3D illustration of branching conditional switches directing value transformations into custom calculated columns
SQL

SQL CASE WHEN: Conditional Logic in SELECT, WHERE, and UPDATE Statements

Searched versus simple CASE, the omitted ELSE that writes NULL in an UPDATE, branch type unification, and pivoting rows into columns.

SQLControl FlowSyntax
3D illustration of atomic transaction capsules protected by escalating isolation level vault walls with deadlock detection monitors
SQL

SQL Transactions: ACID Properties, Isolation Levels, and Deadlock Prevention

Which anomaly each isolation level still allows, why READ COMMITTED permits lost updates, and the lock ordering plus retry loop every deadlock needs.

SQLTransactionsConcurrency
3D illustration of an inner nested query loop validating outer query rows with green boolean EXISTS beacon scanners
SQL

SQL Subqueries: Correlated vs Uncorrelated, EXISTS, IN, and Performance

What makes a subquery run once versus once per row, why NOT IN returns nothing when the inner column has a NULL, and when IN beats a join.

SQLQuery OptimizationPerformance
3D illustration of raw row cubes funneled into aggregated category bins with a selective HAVING sieve barrier
SQL

SQL GROUP BY and HAVING: Aggregation Functions, Filters, and Common Pitfalls

The evaluation order that decides WHERE versus HAVING, why COUNT(*) and COUNT(col) disagree on left joins, and how ROLLUP subtotals hide in NULL.

SQLAggregationQueries
3D illustration of recursive CTE pipelines chaining modular temporary query blocks into an organizational hierarchy
SQL

SQL CTEs: Common Table Expressions and Recursive WITH Queries Explained

When a CTE is inlined and when it becomes an optimisation fence, plus recursive hierarchies with depth and cycle guards and data-modifying CTEs.

SQLQueriesData Structures
3D illustration of a balanced hierarchical B-tree index node tree directing lookup pointers to table disk blocks
SQL

SQL Indexes: B-Tree, Composite Indexes, and Covering Indexes for Query Performance

Why composite column order decides whether an index is used, how a covering index removes the heap fetch, and which indexes to drop because nothing reads them.

SQLPerformanceIndexing
3D illustration of intersecting relational data tables with glowing overlapping intersection zones showing join match conditions
SQL

SQL Joins: INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN with Visual Diagrams

Every join type as a match policy, why a WHERE clause on the right table turns LEFT into INNER, and how row multiplication inflates every aggregate.

SQLRelational DatabaseQueries
3D illustration of partitioned data rows evaluated across sliding window frames with glowing rank badge tags
SQL

SQL Window Functions: ROW_NUMBER, RANK, DENSE_RANK, and LEAD/LAG Explained

How the four ranking and offset functions differ on ties, why the default RANGE frame makes running totals repeat, and how LAG builds gap-and-island detection.

SQLDatabaseAnalytics