Deterministic Ordering
Without a unique sort order, SQL may return tied rows in any order — so top-N lists, pages, and “latest record” picks can change between runs.
-
-
Anti-Join
An anti-join finds rows with no match on the other side — customers who never ordered. One NULL can make NOT IN return nothing; NOT EXISTS does not.
-
When Correct SQL Returns the Wrong Answer
A query can run cleanly and still answer a different question. See how joins, NULLs, ordering, and aggregation levels change results, and how to catch it.
-
Spreadsheet and SQL Tool Choice
Learn which operations the two tools share, which concrete signals say the work has outgrown a sheet, and what moving to SQL actually buys.
-
Database Session
Learn what a database session holds, which objects and settings die with it, and why connection pooling makes session state unreliable.
-
Database View
Learn how a view stores a query instead of rows, which data a read can see, how a materialized view differs, and what it does not guarantee.
-
Recursive CTE
Learn how a recursive CTE builds rows from an anchor query, what stops it, and where generated calendars and hierarchy walks are worth it.
-
CTE Materialization
Learn what it means for a CTE to be materialized or inlined, which engines decide what, and how execution strategy differs from name scope.
-
Temporary Table
Learn what a temporary table stores, how long it lives, why its rows go stale, and how naming and support differ between database engines.
-
CTEs and Temporary Tables
A WITH clause is not a temporary table. Compare scope, lifetime, visibility, and staleness with DuckDB examples.
-
SQL Transaction
Group database changes with commit and rollback, define the transaction boundary, and distinguish atomicity from correct or idempotent work.
-
Window Function and Frame
Separate a window partition, ordering, and frame; distinguish seven rows from seven calendar days and make tied row selection deliberate.
-
Subquery and Common Table Expression
Use nested queries and named WITH results to express SQL dependencies without assuming a fixed execution order or automatic speedup.
-
SQL Aggregation
Define a group, separate WHERE from HAVING, and choose counts, averages, and denominators that match the intended population.
-
Join Cardinality
Predict matching row counts, distinguish unmatched rows from NULL attributes, and prevent repeated order amounts from inflating totals.
-
SQL Type Conversion
Convert text deliberately with CAST and TRY_CAST, preserve failed inputs, and distinguish a valid type from a valid business value.
-
Primary and Foreign Keys
Distinguish row identity from references, understand composite keys, and see which errors key constraints can and cannot prevent.
-
SQL NULL and Three-Valued Logic
Understand SQL NULL, why ordinary comparisons can be unknown, and how filtering, counting, and COALESCE change a report.
-
SQL for Data Engineers
Understand SQL through examples for filtering, cleaning, joins, aggregation, windows, and transactions in DuckDB.