Range & Set Filtering (BETWEEN, IN, NOT IN) · Flow Cadax | Flow Cadax

Beginner/Filtering, Sorting & Operators

High PriorityBeginner 25 min read & practiceFree Preview Sample

Not Started

Short lesson

Range & Set Filtering (BETWEEN, IN, NOT IN)

Range & Set Filtering (BETWEEN, IN, NOT IN): Filter data across continuous numeric/date ranges and discrete value sets using BETWEEN, IN, and NOT IN.

Legacy / Unoptimized Pattern

Unindexed full table scans, non-SARGable wrappers, cursors, missing transaction boundaries, and unhandled deadlocks.

Enterprise Relational Standard

Covering B-Tree index seeks, 3NF schema integrity, deterministic ACID transactions, and sub-millisecond execution plans.

Go deeperDeep-Dive Architecture: Range & Set Filtering (BETWEEN, IN, NOT IN)

6 Parts

Chapters:6 Parts Available

PART 01Foundational Context

1 of 6

Core Architectural Foundation & Operational Principles

BETWEEN filters across an inclusive range, while IN tests whether a column value matches any item in a specified list.

Sub-topic 1

Inclusive boundary behavior of BETWEEN for numbers vs timestamp truncation gotchas with dates

Sub-topic 2

Set matching with IN and the critical danger of NULL values inside NOT IN subqueries

PART 02Technical Breakdown

2 of 6

Visual Execution Architecture & Storage Pipeline

The relational and storage engine execution flow for Range & Set Filtering (BETWEEN, IN, NOT IN):

SQL Execution Plan & Relational Visualizer

Schema / Query Flow

ENTERPRISE SQL PIPELINE (Range & Set Filtering (BETWEEN, ):\n┌────────────────────────────────────────────────────────────────────────┐\n│ Relational Engine: Query Parser -> Algebrizer -> Cost-Based Optimizer  │\n│  • SARGable Predicates  • Execution Plan Cache  • Cardinality Estimates │\n└───────────────────────────────────┬────────────────────────────────────┘\n                                    ▼\n┌────────────────────────────────────────────────────────────────────────┐\n│ Storage Engine: Buffer Pool Caching & B-Tree Page Traversal            │\n│  ┌───────────────────────────────────┐  ┌────────────────────────────┐ │\n│  │ Index Seeks & Covering In-Memory  │  │ Page Locks & Concurrency   │ │\n│  │ 8KB Data Pages / Extents          │  │ MVCC / Snapshot Isolation  │ │\n│  └───────────────────────────────────┘  └────────────────────────────┘ │\n└───────────────────────────────────┬────────────────────────────────────┘\n                                    ▼\n┌────────────────────────────────────────────────────────────────────────┐\n│ Transaction Durability & Persistence: Write-Ahead Logging (WAL)       │\n│  • Atomic COMMIT / ROLLBACK  • LDF Log Flush  • Zero Data Loss         │\n└────────────────────────────────────────────────────────────────────────┘

Swipe horizontally for full architecture⟷

PART 03Technical Breakdown

3 of 6

Under The Hood: How the Database Executes This

The optimizer translates IN lists into an internal hash semi-join or an index seek across discrete list values.

Senior Automation Architect Field Insight

“Use NOT EXISTS instead of NOT IN when filtering against subqueries that may contain NULL values.”

PART 04Technical Breakdown

4 of 6

Enterprise Production Scenario

Retrieving all warehouse shipments scheduled BETWEEN '2026-03-01' AND '2026-03-31' with status IN ('QUEUED', 'PACKED').

PART 05Technical Breakdown

5 of 6

Critical Pitfalls & What to Avoid

Common developer mistake: Using NOT IN when the target list contains a NULL, causing the entire query to evaluate to UNKNOWN and return zero rows.

Pitfall

Using NOT IN when the target list contains a NULL, causing the entire query to evaluate to UNKNOWN and return zero rows.

Enterprise Standard

Use NOT EXISTS instead of NOT IN when filtering against subqueries that may contain NULL values.

PART 06Key Takeaways

6 of 6

Senior Technical Interview Angle

Why does 'WHERE id NOT IN (SELECT parent_id FROM table)' return zero rows if even a single parent_id is NULL?