Skip to content
Skills Library
Data & analytics · Learning & explanation

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.

Repository
AI-SKILLS ↗
Licence
MIT
Last repository change
05.09.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 05.09.2026, without references to retired models or APIs.

Licence

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

  1. Method
  2. Boundaries
Original SKILL.md headings · 39 lines, 299 words
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…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.