null-semantics
/null-semantics
Reason about SQL nulls in comparisons, aggregates, joins, and uniqueness so absent values stop producing wrong or missing rows. Use when a query silently drops rows or a NOT IN returns nothing.
- Author
- Amey-Thakur ↗
- Repository
- AI-SKILLS ↗
- Licence
- MIT
- Last repository change
- 05.09.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 05.09.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
Reason about SQL nulls in comparisons, aggregates, joins, and uniqueness so absent values stop producing wrong or missing rows. Use when a query silently drops rows or a NOT IN returns nothing.
Original author description
Reason about SQL nulls in comparisons, aggregates, joins, and uniqueness so absent values stop producing wrong or missing rows. Use when a query silently drops rows or a NOT IN returns nothing.
How it works
- Method
- Boundaries
SKILL.md preview
--- name: null-semantics description: Reason about SQL nulls in comparisons, aggregates, joins, and uniqueness so absent values stop producing wrong or missing rows. Use when a query silently drops rows or a NOT IN returns nothing. --- # Null semantics Null is not a value but an absence, and SQL's three-valued logic follows from that: comparisons with null are unknown rather than false, and unknown is not true, so the row does not pass. Most null bugs are this one rule applied somewhere you did not expect. ## Method 1. **Test with IS NULL, never with equals.** column = NULL is unknown for every row including the null ones. This is the single most common null mistake. 2. **Expect NOT IN to collapse with nulls.** If the list contains a null, NOT IN yields no rows at all, because the comparison can never be proven true. Use NOT EXISTS instead, which handles it correctly (see sql-joins). 3. **Know which aggregates skip nulls.** Most aggregates ignore null inputs, so an average is over non-null rows only. That is often what you want and often not what you assumed. 4. **Watch nulls flowing from outer joins.** A left join produces nulls for unmatched rows, and any later comparison on those columns is unknown, which turns filters into unintended inner joins. 5. **Decide what null means in each column.** Not applicable, not yet known, and not provided are different, and a schema that conflates them produces queries nobody can write correctly (see schema-design). 6. **Use COALESCE at the edge, not everywhere.** Substituting a default is right at presentation and wrong in the middle of logic, where it hides the distinction you may need. ## Boundaries - Uniqueness treatment of nulls differs between engines, so a unique constraint may or may not permit several nulls. - Sort order for nulls also varies, which changes pagination and top-N results between databases. - Avoiding nulls entirely by using sentinel values trades one problem for a worse one, since sentinels participate in arithmetic.
Versions
The database has no other skills named null-semantics.
Database snapshot: 13.11.2026 · package version 5ae8d7f8f6be (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100sql-joins
Choose and write the right join so rows are neither lost nor multiplied, and know why a result set grew. Use when combining tables and the row count or the null columns look wrong. - 100reviewing-data-transformations-and-contracts
Reviews the analytics/data plane — SQL/dbt transformations, data tests, and event or published-table schemas — for transformation correctness, data-test adequacy, and producer-to-consumer data-contract safety: violated grain and fan-out joins that silently inflate aggregates,… - 100authoring-data-quality-checks
Adds and runs data quality checks (dbt-test style assertions) on a project's warehouse tables and saved-query views, and HogQL catalog metrics: not-null, uniqueness, accepted values, referential integrity, row-count bounds, freshness, and custom HogQL. Metrics support custom SQL… - 95iris-sql
Use when writing, debugging, or optimizing SQL queries in IRIS — table naming, reserved words, NULL semantics, SQLCODE, IN clause limits, procedures, DDL quirks, and date handling. Load explicitly for SQL work; do NOT load globally for general ObjectScript repair tasks. - 100reviewing-data-transformations-and-contracts
Reviews the analytics/data plane — SQL/dbt transformations, data tests, and event or published-table schemas — for transformation correctness, data-test adequacy, and producer-to-consumer data-contract safety: violated grain and fan-out joins that silently inflate aggregates,… - 100schema-null-defaults
Handle NULL values and set default values for missing fields in collections and materializations. Use when fields need defaults, nullable primary key errors occur, or source data has gaps. Use when user says "NULL values", "default value", "missing field", "nullable primary key…