These are the building blocks of WHERE conditions: comparison operators (=, <>, >, <), logical operators (AND, OR, NOT), IN (matches any value in a list), BETWEEN (inclusive range), and LIKE (pattern matching with wildcards). They let testers express precise filters for validation and test-data selection.
Combining conditions with AND/OR is routine, but operator precedence matters: AND binds tighter than OR, so mixing them without parentheses is a classic source of wrong results — always parenthesize mixed AND/OR logic. IN is a concise alternative to many OR conditions (status IN ('confirmed','shipped')). BETWEEN a AND b is inclusive on both ends, which surprises people at boundaries — for exclusive ranges use explicit comparisons. LIKE matches text patterns: % matches any sequence of characters, _ matches a single character (e.g. LIKE 'test%@%' to find test emails). Pattern matching is often case-sensitive depending on the database and collation, so verify behavior. Used well, these operators let a tester pull exactly the rows a scenario needs without over- or under-matching.
To pull all test-account orders in a date range that are active, a tester writes WHERE email LIKE '%@test.com' AND status IN ('confirmed','shipped') AND created_at BETWEEN '2026-07-01' AND '2026-07-31' — combining pattern, list, and range filters precisely.
-- IN instead of multiple ORs
SELECT id, status FROM orders
WHERE status IN ('confirmed', 'shipped', 'delivered');
-- BETWEEN is inclusive of both bounds
SELECT id FROM orders
WHERE total BETWEEN 50 AND 100; -- includes 50 and 100
-- LIKE pattern: find test-account emails
SELECT email FROM users
WHERE email LIKE '%@test.com';
-- Precedence trap — parenthesize mixed AND/OR
SELECT id FROM orders
WHERE (status = 'confirmed' OR status = 'shipped')
AND total > 100;Scenario: A tester wants confirmed or shipped orders over $100 and writes WHERE status = 'confirmed' OR status = 'shipped' AND total > 100. The result wrongly includes confirmed orders under $100. Explain the bug and fix it.