Part II: Retrieving Data Chapter 4: Advanced Filtering and Expressions

 Chapter 4: Advanced Filtering and Expressions

In this chapter, we’ll deepen your SQL toolkit by mastering advanced filtering techniques and expressions in the WHERE clause. Precise filters let you extract exactly the rows you need, avoid noisy results, and improve query performance. We’ll cover:

• Comparison Operators: =, <>, >, <, >=, <=

• Logical Operators: AND, OR, NOT

• Pattern Matching: LIKE with % and _ wildcards

• Set Membership & Ranges: IN and BETWEEN

• Handling Missing Values: IS NULL and COALESCE

By the end, you’ll be crafting tight, readable filters that yield clean, accurate datasets.

1. Comparison Operators

Comparison operators form the bedrock of row-level filtering. They compare a column’s value to a constant or another column.

Operator	Description	Example
=	Equal to	WHERE status = 'active'
<>	Not equal to	WHERE rating <> 5
>	Greater than	WHERE price > 100
<	Less than	WHERE quantity < 10
>=	Greater than or equal to	WHERE age >= 18
<=	Less than or equal to	WHERE created_at <= '2025-01-01'

Example Find orders over $500 that are not “cancelled”:

sql

SELECT order_id, total_amount, status
FROM orders
WHERE total_amount >= 500
  AND status <> 'cancelled';

2. Logical Operators

Combine multiple comparison tests with logical operators:

• AND — all conditions must be true

• OR — at least one condition must be true

• NOT — inverts the truth of a condition

2.1 AND / OR Precedence & Grouping

AND has higher precedence than OR, but using parentheses ensures clarity.

sql

-- Without parentheses: AND evaluated before OR
SELECT * FROM users
WHERE role = 'admin'
  OR role = 'manager'
  AND active = TRUE;

-- Clear intent with parentheses
SELECT * FROM users
WHERE (role = 'admin' OR role = 'manager')
  AND active = TRUE;

2.2 NOT

Negate a condition or group:

sql

-- Users who are not active
SELECT * FROM users
WHERE NOT active;

-- Products not in a certain category
SELECT * FROM products
WHERE NOT (category = 'Electronics');

3. Pattern Matching with LIKE

Use LIKE when you need substring or pattern-based filtering:

• % matches zero or more characters

• _ matches exactly one character

sql

-- Emails at any domain ending in .org
SELECT email
FROM contacts
WHERE email LIKE '%@%.org';

-- Products starting with “Pro” (case-sensitive)
SELECT product_name
FROM products
WHERE product_name LIKE 'Pro%';

-- Four-letter codes ending with “X”
SELECT code
FROM inventory
WHERE code LIKE '___X';

Tip: In PostgreSQL, use ILIKE for case-insensitive matching:

sql

WHERE username ILIKE 'admin_%';

4. Set Membership and Ranges

4.1 IN

IN checks membership against a list of values. It’s cleaner than chaining multiple OR conditions:

sql

-- Orders in January or February 2025
SELECT order_id, order_date
FROM orders
WHERE order_date IN ('2025-01-15', '2025-02-10', '2025-02-28');

-- Customers in specific segments
SELECT customer_id, segment
FROM customers
WHERE segment IN ('enterprise', 'small-business');

4.2 BETWEEN

BETWEEN … AND … filters inclusive ranges. It works with numbers, dates, and text:

sql

-- Sales between $100 and $500
SELECT *
FROM sales
WHERE amount BETWEEN 100 AND 500;

-- Dates in the first quarter of 2025
SELECT *
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-03-31';

Under the hood, x BETWEEN a AND b is equivalent to x >= a AND x <= b.

5. Handling Missing Values

NULL denotes unknown or missing data. Special care is needed when filtering:

5.1 IS NULL / IS NOT NULL

sql

-- Find users without an assigned manager
SELECT employee_id, name
FROM employees
WHERE manager_id IS NULL;

-- Exclude products missing a price
SELECT product_id, name
FROM products
WHERE price IS NOT NULL;

Avoid writing price = NULL—that condition always yields false.

5.2 COALESCE

COALESCE(expr1, expr2, …) returns the first non-NULL expression. Use it to provide defaults in filters or in SELECT lists:

sql

-- Treat missing discounts as zero
SELECT
  order_id,
  COALESCE(discount, 0) AS discount_amount
FROM orders;

-- Filter orders by discount, using zero for NULL
SELECT order_id, total_amount
FROM orders
WHERE COALESCE(discount, 0) > 50;

Common pattern: Replace NULL flags with meaningful defaults so that your filters behave predictably.

6. Composite Examples

Putting these techniques together yields powerful, precise queries.

Example A: Active, High-Spend Customers

Find customers who are active, in the “premium” segment, with lifetime spend between $5,000 and $10,000, excluding test accounts:

sql

SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COALESCE(SUM(o.total_amount), 0) AS lifetime_spend
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.active = TRUE
  AND c.segment = 'premium'
  AND c.email NOT LIKE '%@test.com'
GROUP BY c.customer_id, customer
HAVING lifetime_spend BETWEEN 5000 AND 10000
ORDER BY lifetime_spend DESC;

Example B: Filtering Logs with Multiple Criteria

Fetch the most recent 50 error logs where the source is not “heartbeat” and the message contains “timeout” or “failed”:

sql

SELECT
  log_id,
  source,
  message,
  logged_at
FROM system_logs
WHERE level = 'ERROR'
  AND NOT source = 'heartbeat'
  AND (message LIKE '%timeout%' OR message LIKE '%failed%')
  AND logged_at > NOW() - INTERVAL '7 days'
ORDER BY logged_at DESC
LIMIT 50;

7. Best Practices and Performance Tips

1. Index Filter Columns Columns used frequently in WHERE, IN, or BETWEEN should be indexed to speed up lookups.

2. Avoid Leading Wildcards Patterns like %term prevent index usage. Instead, prefer term% or full-text search.

3. Simplify OR Conditions Replace multiple OR clauses on the same column with IN(...). This often generates a more efficient plan.

4. Use COALESCE Judiciously Overusing COALESCE in filters can lead to full table scans. Only coalesce when necessary.

5. Short-Circuit Logic In AND chains, place the most selective conditions first to reduce row evaluation quickly.

6. Test Filters Separately Develop complex filters incrementally. First run each condition alone, then combine.

Advanced filtering and expressions transform raw tables into targeted datasets. By combining comparison and logical operators with pattern matching, set membership, and null handling, you can write expressive, performant queries that deliver exactly the data you need. In the next chapter, we’ll explore aggregation and grouping to summarize and analyze your filtered results.