Comparison Operators and Wildcards with LIKE

Equality is the easy half of WHERE. The trickier half is partial matches — "emails ending in @test.com," "products containing the word 'phone' anywhere in the name," "orders above some threshold." Two tools cover almost everything: numeric comparison operators (>, <, >=, <=, !=) and the pattern-matching LIKE operator. Used together they make WHERE flexible enough for almost every test verification you'll write.

Numeric comparison operators

You met = and != last lesson. Here's the rest:

!= and <> mean exactly the same thing — <> is the original SQL spelling, != is the modern one most databases also accept. Pick one and stick with it.

A few practical examples:

-- Products $50 or more
SELECT name, price FROM products WHERE price >= 50.00;

Result:

| name                     | price  |
|--------------------------|--------|
| Mechanical Keyboard Pro  | 120.00 |
| 27-inch Monitor          | 320.00 |
| Standing Desk Mat        |  55.00 |
| Webcam HD                |  75.00 |

-- Orders that were not zero-value (sanity check for a refund test)
SELECT id, total, status FROM orders WHERE total != 0;

-- Products created in the last 30 days
-- (date math is database-specific; this works in PostgreSQL/SQLite)
SELECT name, created_at FROM products
WHERE created_at >= DATE('now', '-30 days');

These operators work on numbers, dates, and even strings (where they compare alphabetically — 'A' < 'B' < 'C'). The most common QA use is with numeric thresholds: prices, quantities, counts, durations.

LIKE — pattern matching for text

Equality finds exact matches. LIKE finds patterns — text that starts with, ends with, or contains a sequence of characters. It uses two wildcards:

• % matches any sequence of characters, including zero. ('A%' matches Alice, Abel, and A.)
• _ matches exactly one character. ('A___e' matches Alice and Abode — but not Anne.)

Some real queries on the e-commerce database:

-- Names starting with 'A'
SELECT name FROM users WHERE name LIKE 'A%';

Result:

| name        |
|-------------|
| Alice Khan  |

-- Test-account emails (anything @test.com)
SELECT email FROM users WHERE email LIKE '%@test.com';

Result:

| email             |
|-------------------|
| esha+qa@test.com  |

-- Products with 'Pro' anywhere in the name (catches "Pro", "Professional", etc.)
SELECT name, price FROM products WHERE name LIKE '%Pro%';

Result:

| name                     | price  |
|--------------------------|--------|
| Mechanical Keyboard Pro  | 120.00 |
| SQL for Testers (book)   |  18.50 |  -- if it had "Pro" in the name; otherwise not

Wait — in our seed data, only one product has "Pro" in its name: the keyboard. The book example above wouldn't match. (Worth running yourself to see the real result!)

-- Names with exactly 5 characters
SELECT name FROM users WHERE name LIKE '_____';
-- Returns nothing in our sample — all our names are longer than 5

Case sensitivity — a real dialect difference

LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL by default. So WHERE name LIKE 'a%' returns Alice in MySQL but nothing in PostgreSQL. Two ways to deal with it portably:

• PostgreSQL: use ILIKE (case-insensitive LIKE) — WHERE name ILIKE 'a%'.
• Anywhere: lowercase both sides — WHERE LOWER(name) LIKE 'a%'. Slower on huge tables, but it works on every database.

For day-to-day queries, just remember: if a LIKE query returns fewer rows than you expected, suspect case before you suspect anything else.

NOT LIKE — exclude patterns

Flip the condition by adding NOT:

-- All users whose email isn't from a test domain
SELECT name, email FROM users WHERE email NOT LIKE '%@test.com';

Result:

| name        | email             |
|-------------|-------------------|
| Alice Khan  | alice@example.com |
| Bob Patel   | bob@example.com   |
| Carol Singh | carol@example.com |
| Dan Müller  | dan@example.com   |

NOT LIKE is invaluable for excluding test data when generating production reports — "give me real users, not the ones the QA team created."

Combining everything in one query

The real power comes from chaining conditions with AND:

SELECT name, price FROM products
WHERE price >= 10.00
  AND price <= 100.00
  AND name LIKE '%a%';

That's "products between $10 and $100 with at least one lowercase 'a' in the name." Nothing fancy — just three conditions joined together. We'll go deeper on AND, OR, IN, and BETWEEN in Chapter 3.

QA use cases for LIKE

A few patterns testers reach for daily:

• Find test data: WHERE email LIKE '%@qa-test.local' finds every account your team created.
• Find users matching a naming convention: WHERE name LIKE 'Test User%' finds everything your test fixtures generated.
• Search error messages or descriptions: WHERE message LIKE '%timeout%' surfaces every row that mentions a timeout.
• Sanity-check product catalogues: WHERE name LIKE '%TODO%' or WHERE name LIKE '%placeholder%' finds the rows nobody finished writing.

⚠️ Common Mistakes

• Forgetting %. WHERE email LIKE 'test' finds rows where email is exactly the literal string 'test' — not what you want. Use '%test%' to find "test" anywhere in the value.
• Case surprises. A LIKE pattern that works in MySQL fails on the same data in PostgreSQL because of casing. Use ILIKE (Postgres) or LOWER(...) if you don't want to be tripped up by capitalisation.
• Treating LIKE as regex. LIKE only knows % and _. If you need [abc], alternation, or quantifiers, you want regex (SIMILAR TO, ~, or REGEXP — all database-specific). LIKE is intentionally simple.

🎯 Practice Task

25 minutes. All queries against the e-commerce database.

1. Find products priced between $20 and $80 (use two >= / <= conditions joined with AND).
2. Find all users whose name contains 'Singh'.
3. Find products whose name starts with 'M'.
4. Find emails not using @example.com — useful for spotting non-team test accounts.
5. Find products that are out of stock and priced over $40.
6. Run SELECT name FROM products WHERE name LIKE 'usb-c hub'; (lowercase). On your database, does it find the row? Try LIKE 'USB-C%' instead. What's different? (This shows you whether your database is case-sensitive.)
7. Stretch: Find products containing the word 'Mat' — careful, it might match more than you expect. Look at the result and decide: is this a feature or a bug? ('Standing Desk Mat' matches; 'Mat' is a substring.)

Next lesson: ordering results so the useful rows come first, and limiting to just the top N — exactly what you need to find "the most recent order" or "the top 5 most expensive products."