9. Using IN and BETWEEN

Core Idea: The IN operator filters records matching any value in a discrete set, while BETWEEN tests whether an expression falls within an inclusive numerical, string, or date range.

Syntax Structure

SELECT * FROM Products WHERE Category IN ('Electronics', 'Home', 'Office');

SELECT * FROM Products WHERE Price BETWEEN 100 AND 200;

Combining Multiple Conditions

            SELECT * FROM Products WHERE Category IN ('Electronics', 'Appliances') AND Price BETWEEN 50 AND 150;
        

Practical Scenarios

Filtering items by user-selected multiple category checkboxes, restricting transactions to a quarterly budget range, or querying order activity between two dates.

Advanced Deep Dive ADVANCED

BETWEEN Boundaries: BETWEEN is strictly inclusive at both ends (equivalent to >= lower AND <= upper).

IN List Performance & Parameter Limits: Large IN (...) lists with hundreds or thousands of items can cause query compilation bottlenecks; consider staging IDs in a temporary table or table-valued parameter (TVP).

Three-Valued Logic with NULLs: IN will never match NULL values unless an explicit OR Col IS NULL is added. Beware of NOT IN (..., NULL) which evaluates to UNKNOWN for all rows.

BETWEEN with Datetime: When querying DATETIME, BETWEEN '2024-01-01' AND '2024-01-31' only matches midnight of January 31st. Prefer half-open intervals: DateCol >= '2024-01-01' AND DateCol < '2024-02-01'.

Plan Reuse: Parameterizing discrete IN list inputs helps the query optimizer reuse cached query plans safely.