4. Filtering Rows with WHERE

4. Filtering Rows with WHERE

Idea: WHERE trims rows. You describe a condition; only matching rows survive.

Shape

SELECT Column1 FROM TableName WHERE Column1 = 5;

Operators

NULL Note

NULL means unknown. You check with IS NULL or IS NOT NULL.

Placeholders for Examples

Coming soon: filtering products cheaper than a value.

Advanced Deep Dive ADVANCED

SARGability: A predicate is SARGable when the optimizer can use indexes efficiently (e.g., Column1 = 5). Wrapping the column in a function (LEFT(Column1,2)='AB') often forces scans.

Three-Valued Logic: Comparisons with NULL yield UNKNOWN, not TRUE/FALSE. WHERE keeps only TRUE rows; UNKNOWN rows are discarded.

Seek vs Scan: Execution plans show whether an index is navigated precisely (seek) or read fully (scan). Predicate form influences this.

Parameter Sniffing: In procedures, first parameter values shape cached plans, sometimes hurting later executions with different data distribution. Techniques: OPTION (RECOMPILE), local variables, or plan guides (use sparingly).

IN vs EXISTS: For large subquery sets, EXISTS can short-circuit after first match; IN may materialize a list. Understand semantics to choose best form.

next