Filtering & Operators in SQL

Data Filtering, Predicates, and Boolean Evaluation The power of SQL lies in its ability to parse millions of data rows on a storage disk and return only the precise records requested by an application. This search optimization is handled in the WHERE clause using operators and predicates . These filters evaluate every row against a boolean expression, allowing only rows that return a TRUE state to pass through to the final result set. 1. Mathematical & Comparative Operations Comparison operators form the baseline structure for filtering data by evaluating how two values relate to one another. Operator Structural Role Operational Query Example = Exact Equality WHERE status_code = 200 != or <> Inequality / Non-Match WHERE node_role != 'Gateway' > / < Greater Than / Less Than WHERE traffic_weight > 500 >= / <= Greater Than or Equal / Less Than or Equal WHERE uptime_percentage >= 99.9 2. Composite Filtering: Logical Operators When an business logic requirement depends on multiple criteria, Logical Operators combine separate comparative checks into a single cohesive boolean expression. AND : Returns TRUE only if all joined conditions evaluate to TRUE . OR : Returns TRUE if at least one of the joined conditions evaluates to TRUE . NOT : Inverts the boolean output of a condition (turning a TRUE statement into FALSE , and vice versa). SQL SELECT node_id, assigned_ip FROM system_nodes WHERE is_active = TRUE AND (traffic_weight > 500 OR node_role = 'Primary') AND NOT node_status = 'DEGRADED'; Order of Operations Guardrail: SQL evaluates logical operators in a strict hierarchical sequence: NOT runs first, followed by AND , and finally OR . You must use parentheses () around your OR conditions to explicitly control the evaluation order, preventing subtle data leakage bugs. 3. Range and List Iteration Filters ( BETWEEN , IN ) A. Boundary Matching: BETWEEN The BETWEEN operator filters values within a specific range, including the start and end values (it is inclusive ). It is a cleaner, shorthand equivalent to writing a combined >= and <= statement. SQL -- Inclusive Range Selection: Matches everything from 100 up to and including 500 SELECT node_name, system_weight FROM system_nodes WHERE system_weight BETWEEN 100 AND 500; B. Set Matching: IN & NOT IN The IN operator checks if a column's value matches any item within a pre-defined list or the result set of an internal subquery. SQL -- IN: Returns rows where the field matches any item in the target array SELECT node_name, assigned_ip FROM system_nodes WHERE node_role IN ('Gateway', 'LoadBalancer', 'EdgeRouter'); -- NOT IN: Excludes records that match items in the target array SELECT node_name FROM system_nodes WHERE node_status NOT IN ('DEGRADED', 'OFFLINE'); The Critical NOT IN Null Trap If your target list or subquery inside a NOT IN clause contains even a single NULL value, the entire query will instantly return 0 rows . This happens because SQL evaluates NOT IN (1, 2, NULL) as value != 1 AND value != 2 AND value != NULL . Since any comparison against NULL results in an UNKNOWN state, the combined condition can never evaluate to TRUE . Always ensure your target data set filters out nulls beforehand. 4. Pattern Matching: LIKE & Wildcards When you need to search for text patterns rather than exact matches, the LIKE operator scans string values using two native wildcard placeholders: % (Percent Sign): Represents any number of characters (zero, one, or multiple characters). _ (Underscore): Represents exactly one single character. SQL -- 1. Matches any string starting with 'Node-' followed by any trailing text SELECT node_name FROM system_nodes WHERE node_name LIKE 'Node-%'; -- 2. Matches any string where 'Edge' appears anywhere within the text SELECT node_name FROM system_nodes WHERE node_name LIKE '%Edge%'; -- 3. Matches exact structural patterns (e.g., 'Zone-' followed by exactly 2 trailing characters) SELECT node_name FROM system_nodes WHERE zone_code LIKE 'Zone-__'; Case Sensitivity: The standard LIKE operator is completely case-sensitive in engines like PostgreSQL. To run a case-insensitive search without mutating your strings using functions like LOWER() , use the ILIKE operator instead. 5. Subquery Evaluation Predicates ( EXISTS , ANY , ALL ) These advanced predicates evaluate conditions against records returned from an internal subquery structure. A. High-Performance Structural Checking: EXISTS The EXISTS operator tests whether a subquery returns any rows at all. It does not look at the actual data values inside the rows; it simply looks for the existence of a matching record. The moment the database engine finds a single matching row inside the subquery, it stops scanning immediately and returns TRUE . This makes EXISTS highly efficient for validation checks on large tables. SQL SELECT company_name FROM corporate_entities c WHERE EXISTS ( -- Internal correlation scan looks for matching child relationships SELECT 1 FROM system_nodes n WHERE n.parent_company_id = c.company_id AND n.traffic_weight > 800 ); B. Multi-Value Array Comparisons: ANY / ALL ANY : Evaluates to TRUE if the comparison is successful against at least one of the values returned by the subquery (functions identically to a dynamic OR chain). ALL : Evaluates to TRUE only if the comparison is successful against every single value returned by the subquery (functions identically to a dynamic AND chain). SQL -- ANY: Finds nodes with a weight greater than at least one node in the staging pool SELECT node_name FROM system_nodes WHERE traffic_weight > ANY (SELECT traffic_weight FROM staging_nodes); -- ALL: Finds nodes with a weight greater than every single node in the staging pool combined SELECT node_name FROM system_nodes WHERE traffic_weight > ALL (SELECT traffic_weight FROM staging_nodes); Filtering Predicates Reference Matrix Operator System Vector Primary Target Matching Focus Index Optimization Potential Critical Production Guardrail BETWEEN Continuous range bounds (Inclusion matching). High. Can leverage standard B-Tree index scanning fields smoothly. Ensure the low boundary value is placed first ( BETWEEN 10 AND 50 ). Reversing the order ( BETWEEN 50 AND 10 ) will return 0 records. IN Explicit list set iteration matching. High. Translates internally into structured index point lookup paths. Keep literal lists to a reasonable size. Passing thousands of explicit IDs inside an IN() array can cause high query parsing overhead. LIKE '%Text' Suffix / Left-open pattern matching. Zero Index Use. Triggers a slow, full-table scan across the entire disk. Avoid leading wildcards ( %pattern ) in high-frequency queries. If the wildcard is at the start, the engine cannot use a standard index and must scan the entire table row-by-row. IS NULL Missing or unassigned data states. Variable depending on engine setup. Never use standard comparative equals math symbols ( = NULL ). You must write IS NULL or IS NOT NULL to match unknown database states. EXISTS Subquery record existence checks. High. Short-circuits the scan path the moment it finds the first matching row. Prefer EXISTS over IN when checking for the existence of data in large child tables, as it handles null values safely and performs better.