Basics
Filtering — AND, OR, NOT, IN, BETWEEN, LIKE
Filtering allows you to subset data by defining logical criteria that each record must satisfy to be included in your results. By using specialized operators, you can build precise queries that isolate specific business entities or temporal ranges from massive datasets. This skill is essential for performance optimization and generating accurate reports, as it minimizes the volume of data processed by the server.
Logical Connectives: AND, OR, and NOT
Logical operators are the building blocks of SQL filtering, allowing you to combine multiple conditions to refine result sets. The 'AND' operator requires all connected conditions to be true, effectively narrowing your results by increasing the specificity of the query. Conversely, 'OR' is inclusive, returning any row that meets at least one of the provided conditions, which expands the result set. The 'NOT' operator flips the truth value of a condition, allowing you to exclude specific patterns or values. When combining these, the database engine follows standard precedence rules, where 'AND' is evaluated before 'OR'. To ensure predictable results, always use parentheses to explicitly define the order of operations, especially when mixing these operators. Reasoning about these filters involves thinking in terms of Boolean logic gates; visualizing how records either pass or fail these gates helps in writing complex, bug-free query logic.
-- Select active users in a specific region
SELECT user_id, email
FROM users
WHERE status = 'active' AND region_code = 'US';
-- Select high-value orders or those from a specific partner
SELECT order_id
FROM orders
WHERE order_total > 500 OR partner_id = 99;
-- Exclude testing accounts
SELECT user_id
FROM users
WHERE NOT status = 'test';Set Membership with IN
The 'IN' operator provides a concise way to match a column against a list of multiple discrete values, effectively serving as a shorthand for multiple 'OR' statements. When you use 'IN', you are telling the database to return a row if the target field matches any value contained within the provided set. This is significantly more readable than chaining 'OR' conditions, especially when checking against a long list of identifiers or status codes. Behind the scenes, the database evaluates whether the column value is contained within the provided collection. It is important to note that 'IN' handles NULL values carefully; if your list contains NULL, it does not act as a match unless explicitly handled. Using 'IN' is highly efficient for lookup operations compared to writing complex boolean chains, and it is a fundamental tool for writing clear, maintainable SQL scripts during data analysis.
-- Find orders belonging to specific priority customers
SELECT order_id, status
FROM orders
WHERE customer_type IN ('enterprise', 'government', 'wholesale');Range Matching with BETWEEN
The 'BETWEEN' operator is a specialized syntax for inclusive range filtering, designed to retrieve values that fall within a defined start and end point. Unlike 'IN', which looks for membership in a set, 'BETWEEN' is optimized for ordinal data types such as numbers, dates, and timestamps. It is equivalent to using a combination of the 'greater than or equal to' and 'less than or equal to' operators. The inclusivity is a critical aspect; both the lower and upper bounds are included in the search range. When using 'BETWEEN' with dates, you must be precise regarding time components, as a timestamp of '2023-01-01 12:00:00' would be excluded if you only specify '2023-01-01' as the upper bound. Mastering this operator allows for cleaner syntax when filtering for temporal trends, financial quarters, or numerical metrics, ensuring your logic remains easy to interpret.
-- Get transactions within a specific month
SELECT transaction_id, amount
FROM transactions
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31';Pattern Matching with LIKE
The 'LIKE' operator enables fuzzy searching by comparing string columns against a pattern containing wildcards. The percent sign (%) represents zero, one, or multiple characters, while the underscore (_) represents exactly one character. This operator is crucial for identifying records where the exact value is unknown, such as searching for emails by domain or identifying product names with specific prefixes. Because 'LIKE' performs pattern analysis, it can be computationally expensive if the pattern starts with a wildcard, as it prevents the database from utilizing indexes efficiently. When reasoning about 'LIKE', think of it as a template engine; the database iterates through the rows to verify if the text structure matches your defined template. It is a powerful tool for exploratory data analysis, but always aim to use specific patterns to ensure your queries remain performant as your dataset grows to millions of rows.
-- Find all users with a specific corporate domain
SELECT username, email
FROM users
WHERE email LIKE '%@company.com';
-- Find product codes starting with 'SKU' followed by any three digits
SELECT product_name
FROM inventory
WHERE sku_code LIKE 'SKU___';Combined Complexity and Best Practices
As you integrate 'AND', 'OR', 'NOT', 'IN', 'BETWEEN', and 'LIKE', your queries will inevitably become more complex. The primary risk in combining these filters is logical ambiguity. When mixing 'AND' and 'OR', the 'AND' is evaluated first, which can lead to unexpected inclusion or exclusion of rows if parentheses are omitted. To avoid these issues, always document your filtering intent with explicit grouping. Furthermore, consider the performance impact of your filters; sorting data or filtering on non-indexed columns with broad patterns can significantly delay result sets. By combining these operators, you create sophisticated business rules directly within the database layer. This approach is superior to filtering application-side because it minimizes data transfer overhead and leverages the database engine's native optimization capabilities. Keep your logic as simple as possible to ensure that others can audit your code during team reviews.
-- Complex filter for high-value sales in a specific timeframe
SELECT order_id, customer_id
FROM orders
WHERE (order_total BETWEEN 100 AND 1000)
AND (status IN ('shipped', 'delivered'))
AND (customer_email LIKE '%@gmail.com');Key points
- Logical operators like AND, OR, and NOT allow for the combination of multiple distinct filtering criteria.
- Parentheses should be used to explicitly define precedence and avoid ambiguity when mixing different operators.
- The IN operator provides a cleaner, more readable alternative to chaining multiple OR statements.
- The BETWEEN operator is inclusive, meaning it considers both the defined start and end values as valid matches.
- Wildcards in LIKE patterns enable searching for partial string matches when exact values are unknown.
- The percent sign wildcard matches any sequence of characters, while the underscore matches a single character.
- Filtering should happen at the database level to minimize data transfer and take advantage of query optimization.
- Complex filters must be written with performance in mind to avoid unnecessary full-table scans on large datasets.
Common mistakes
- Mistake: Using OR when implying AND for filtering. Why it's wrong: Users often think 'Get me products that are red OR blue' means 'Get me products that are red AND blue' simultaneously, which is impossible for a single column value. Fix: Use AND for conditions that must all be met, or IN for multiple possible values of the same column.
- Mistake: Misunderstanding NOT operator precedence. Why it's wrong: Placing NOT before a logical expression can inadvertently negate the entire WHERE clause if parentheses are missing. Fix: Always use parentheses to group conditions clearly, e.g., WHERE NOT (status = 'Active').
- Mistake: Treating NULL as equal to a value in a list or range. Why it's wrong: NULL represents an unknown value, so it is never equal to, greater than, or less than anything. Fix: Use IS NULL or IS NOT NULL to handle missing data.
- Mistake: Misusing wildcards with LIKE. Why it's wrong: Users often use the underscore (_) when they mean the percentage (%) sign, or vice versa, leading to empty result sets. Fix: Use _ for a single character match and % for zero or more characters.
- Mistake: Including the upper bound in a BETWEEN range when it should be exclusive. Why it's wrong: BETWEEN is inclusive of both endpoints, which can lead to off-by-one errors in date or ID ranges. Fix: Use greater than (>) and less than (<) operators if strict inequality is required.
Interview questions
How do the AND and OR operators function differently when filtering data in a WHERE clause?
The AND operator is a logical conjunction that requires every single condition specified in the query to be true for a row to be included in the final result set. In contrast, the OR operator functions as a logical disjunction, meaning that if at least one of the conditions separated by the OR operator evaluates to true, the row will be returned. For example, if you write 'SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000', the database only retrieves records meeting both criteria. However, if you use 'OR', the database returns records that meet either requirement, which significantly broadens the scope of your result set.
Explain the purpose of the NOT operator and provide a practical scenario for its usage.
The NOT operator is used to negate a boolean condition, effectively reversing the filtering logic of your query. It is essential when you want to retrieve records that do not match a specific criteria rather than trying to define every possible inclusion. A common scenario is identifying active users by excluding those marked as deleted. You would write 'SELECT * FROM users WHERE NOT status = 'deleted''. This is often cleaner and more efficient than listing every other possible status, especially if your application adds new user states over time, as it ensures you do not inadvertently miss records that should have been captured.
When is it more appropriate to use the IN operator versus a series of OR conditions?
The IN operator is generally more appropriate and readable when you need to filter a column against a large list of discrete values. Instead of writing 'status = 'active' OR status = 'pending' OR status = 'archived', you can simply write 'status IN ('active', 'pending', 'archived')'. Beyond just improving code readability and reducing the potential for syntax errors, many query optimizers handle the IN clause more efficiently because it simplifies the parsing process. If your list of values is dynamic or quite long, the IN operator is definitively the industry standard approach for cleaner, more maintainable SQL queries.
How does the BETWEEN operator handle boundary values, and why is it sometimes considered safer than using greater-than or less-than comparisons?
The BETWEEN operator is an inclusive filter, meaning it includes both the start and end values specified in the range. If you query 'SELECT * FROM sales WHERE amount BETWEEN 100 AND 500', the result set includes sales of exactly 100 and exactly 500. It is often safer than using comparison operators like 'amount >= 100 AND amount <= 500' because it minimizes the risk of 'off-by-one' errors or forgetting to include one of the boundaries. Using BETWEEN makes the developer's intent clear, signaling that the entire inclusive range is the specific focus of the filtering logic.
Compare using the LIKE operator with wildcards against using exact equality for string filtering.
The exact equality operator ('=') is used when you know the precise value you are searching for, making it highly efficient for indexing. Conversely, the LIKE operator is used for pattern matching when you only have partial information. For example, 'LIKE 'J%' would find all names starting with 'J'. While LIKE is powerful for search features, it is generally much slower than exact equality, especially if the wildcard is placed at the start of the string, such as '%son', because the database cannot utilize standard B-tree indexes effectively. Therefore, you should always prefer exact equality when the full value is known.
Explain how the order of operations—specifically the precedence of AND over OR—can cause logical bugs if not handled properly with parentheses.
In SQL, the AND operator has higher logical precedence than the OR operator, meaning it is evaluated first unless parentheses are used to override this. If you write 'SELECT * FROM products WHERE category = 'Electronics' OR category = 'Furniture' AND price < 100', the database interprets this as 'Electronics' OR ('Furniture' AND price < 100). This returns all electronics, regardless of price, which is likely not the desired result. To fix this, you must use parentheses: 'SELECT * FROM products WHERE (category = 'Electronics' OR category = 'Furniture') AND price < 100'. Mastering these groupings is crucial for preventing critical data retrieval errors in complex reporting queries.
Check yourself
1. If you want to retrieve rows where 'price' is between 10 and 20, which of the following is logically equivalent to the BETWEEN operator?
- A.price > 10 AND price < 20
- B.price >= 10 AND price <= 20
- C.price >= 10 OR price <= 20
- D.price > 10 OR price < 20
Show answer
B. price >= 10 AND price <= 20
BETWEEN is inclusive, meaning it checks for values greater than or equal to the start and less than or equal to the end. The first option excludes the bounds, the third creates an overly broad condition, and the fourth creates a near-universal match.
2. Which query correctly finds names that start with 'J' and contain an 'n' anywhere after the 'J'?
- A.name LIKE 'J%n%'
- B.name LIKE 'J_n'
- C.name LIKE 'J%n'
- D.name LIKE 'J_n%'
Show answer
A. name LIKE 'J%n%'
The first option uses % to represent any number of characters between J and n, and after n. The second and fourth options enforce only a single character between them, and the third fails if there are characters after the 'n'.
3. Why would the clause 'WHERE status IN ('Open', 'Closed', NULL)' fail to return rows where status is NULL?
- A.NULL cannot be used inside an IN clause
- B.The IN clause treats NULL as an empty string
- C.NULL is not equal to any value, so it must be checked with IS NULL
- D.The SQL engine automatically ignores NULLs in lists
Show answer
C. NULL is not equal to any value, so it must be checked with IS NULL
In SQL, NULL represents an unknown value. Comparisons using = or IN are never true for NULL. You must explicitly use 'OR status IS NULL' to catch those records.
4. Given 'WHERE x > 5 OR y < 10 AND z = 1', how does SQL evaluate this due to operator precedence?
- A.(x > 5 OR y < 10) AND z = 1
- B.x > 5 OR (y < 10 AND z = 1)
- C.(x > 5 OR y) < (10 AND z = 1)
- D.The query will throw a syntax error
Show answer
B. x > 5 OR (y < 10 AND z = 1)
SQL processes AND before OR. Therefore, the y and z condition is evaluated first as a single logical unit before being ORed with the x condition.
5. Which condition effectively selects all rows except those where 'category' is 'Electronics'?
- A.NOT category = 'Electronics'
- B.category != 'Electronics'
- C.category <> 'Electronics'
- D.All of the above
Show answer
D. All of the above
All three expressions are valid standard SQL ways to express inequality. They will all correctly exclude 'Electronics' but, importantly, they will also exclude NULL values in the 'category' column.