The snippet library.

SQL ServerRelationships & missing data

Find parents whose every child meets a rule ↗

SQL Server: Combine existence with the absence of a violating child to exclude parents with no children.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Find parents with at least two child rows ↗

SQL Server: Use a correlated count when testing a per-parent threshold.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Find records without any related rows ↗

SQL Server: NOT EXISTS avoids the NULL pitfalls of NOT IN when the subquery column can be missing.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Generate every combination of two sets ↗

SQL Server: CROSS JOIN creates one result per possible pair, so the output size is the product of the input sizes.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Include zero counts for missing relationships ↗

SQL Server: Count the non-null child key after a left join so a missing relationship produces zero.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Join a parent to pre-aggregated totals ↗

SQL Server: Aggregate the child rows first to produce at most one joined row per parent.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Join through a lookup table ↗

SQL Server: Use explicit aliases and join keys when enriching records through two relationships.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Keep parent rows while filtering joined children ↗

SQL Server: Put a child filter in ON when unmatched parents must remain in the result.

SQL Server✓ Tested

SQL ServerRelationships & missing data

List unordered pairs without duplicate reversals ↗

SQL Server: Join using an increasing key comparison to omit self-pairs and reversed duplicates.

SQL Server✓ Tested

SQL ServerRelationships & missing data

Pair employees with their managers ↗

SQL Server: Alias a table twice to represent two different roles in one relationship.

SQL Server✓ Tested