Baldwin Apps

Posted on Jun 2

SQL Pattern Series #2: The Match Pattern

#datascience #sql #database #programming

Choosing between exact matches and pattern matches

SQL Pattern Series #2 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

The difference between exact matching and pattern matching
When to use =
When to use LIKE
Why a small operator change can change the meaning of a query

Most SQL developers run into this distinction eventually.

= and LIKE solve different problems.

= checks for an exact value.

LIKE checks whether a value fits a pattern.

That difference matters when the question changes from:

Is this exactly Admin?

to:

Does this start with Admin?

The image above summarizes the core idea:

= asks whether a value is exactly equal.
LIKE asks whether a value matches a pattern.
Both are useful, but they answer different questions.

Exact Match Example

Use = when the value must match exactly.

SELECT *
FROM Users
WHERE Role = 'Admin';

This query asks:

Is the role exactly Admin?

It will match:

Admin

But it will not match:

AdminAssistant
Administrator
SuperAdmin

That is the point.

The = operator is precise.

Pattern Match

Use LIKE when the value may vary but still follows a recognizable pattern.

SELECT *
FROM Users
WHERE Role LIKE 'Admin%';

This query asks:

Does the role start with Admin?

It may match values like:

Admin
AdminAssistant
Administrator

The % wildcard means:

Any number of characters may appear here.

So Admin% means:

Starts with Admin.

The Match Pattern

The Match Pattern is about choosing the right kind of comparison for the question being asked.

Sometimes you need precision.

Sometimes you need flexibility.

The pattern is simple:

Use = for exact matches
Use LIKE for pattern-based matches

But the effect can be significant.

A query that is too strict may miss valid rows.

A query that is too broad may return rows you did not intend to include.

Why This Pattern Matters

This distinction shows up in many real-world queries.

For example:

Find one exact status
Find names that start with a prefix
Find emails from a specific domain
Find product codes with a shared pattern
Find log messages containing a phrase

Each of these questions requires a different kind of match.

Common Examples

Exact status match

SELECT *
FROM Orders
WHERE Status = 'Pending';

This asks for orders where the status is exactly Pending.

Prefix match

SELECT *
FROM Users
WHERE Username LIKE 'test%';

This asks for usernames that start with test.

Contains match

SELECT *
FROM Products
WHERE ProductName LIKE '%keyboard%';

This asks for product names that contain keyboard anywhere in the value.

Suffix match

SELECT *
FROM Customers
WHERE Email LIKE '%@example.com';

This asks for email addresses that end with @example.com.

A Note on Performance

Pattern matching can be powerful, but it can also affect performance.

For example:

WHERE Username LIKE 'Admin%'

may be easier for a database to optimize because the pattern has a fixed beginning.

But this:

WHERE Username LIKE '%Admin%'

can be more expensive because the database may need to search inside the value rather than starting from the beginning.

The exact behavior depends on:

the database system
indexes
collation settings
data size
query plan

As always, validate assumptions with real execution plans and real workloads.

When I Reach for This Pattern

I typically use = when:

I need one exact value
The column contains controlled values
The comparison should be strict
The query should not include partial matches

I typically use LIKE when:

I need prefix matching
I need suffix matching
I need contains matching
The data is text-based and variable
I am intentionally searching for a pattern

Key Takeaway

Small operator difference.

Very different behavior.

When writing SQL, ask:

Am I looking for an exact value?

or:

Am I looking for a pattern?

That question determines whether the query should use = or LIKE.

The better you understand the match you need, the easier it becomes to write queries that return the right rows.