SFMC SQL Where

Use WHERE to make your query lean and to the point. Less is more.

With just SELECT and FROM statements, you can already start building your queries. With the addition of JOIN, you can do much more than in the Salesforce Marketing Cloud UI. WHERE grants you even more power by allowing you to filter the outcomes of your queries.

The concept behind it is straightforward. It takes all the results you obtained with the query above the WHERE, and for each, it applies one or more conditions. If they evaluate to TRUE - this particular result will be available in the output Data Extension. If evaluation ends with FALSE, it will be filtered out.

All subscribers with domain equal to 'mateuszdabrowski.pl'
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE Domain = 'mateuszdabrowski.pl'

Basic operators

In many scenarios, you will be using the basic operators in your WHERE clauses. In the example above, you already saw the equality operator (=). There are a few more available.

OperatorDescription
=Equal to
<>Not equal to
>Greater than
<Less than
>=Greater or equal to
<=Less or equal to

Equality operators

Equality operators (=, <>) are great when you have a controlled value. Think booleans (for example Opt-in information) and picklists (for example Status field on _Subscribers System Data View that is limited to four values).

All subscribers that do not have the unsubscribed status
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE Status <> 'unsubscribed'

Comparison operators

Comparison operators (<, >, >=, <=) are perfect when you are operating on numbers (for example selecting contacts with low Customer Lifetime Value) or dates.

All subscribers that joined within the last month
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE CONVERT(DATE, DateJoined) >= DATEADD(MONTH, -1, CONVERT(DATE, GETUTCDATE()))

With dates, you will frequently want to use a bit more complex structure for the comparison to work correctly. In this case, we are converting the DateJoined value to simple date (day, month, year without the time). We are also getting a current date in UTC format and convert it to a simple date too. Next, we subtract one month and compare it to the DateJoined transformed value. I cover more such examples in the SFMC SQL Functions.

LIKE operator

Another handy operator is LIKE. It allows you to leverage wildcards to search for matching records based on value fragment. Think about filtering with just a part of a text (for example email domain in EmailAddress field) or with a single element within multi picklist (for instance finding one value within the comma-separated list of all purchased products).

To use LIKE operator, you need to leverage at least one of the two available wildcards:

WildcardDescription
%Zero, one or more characters
_Exactly one character

For example, you can search for %@mateuszdabrowski.pl in EmailAddress to get all contacts with an email address ending with @mateuszdabrowski.pl. However, you can change this filter to %@mateuszdabrowski%. With the second wildcard at the end, it will be looking for any email address that contains @mateuszdabrowski but may have more text on either side of it. It will capture @mateuszdabrowski.pl domain just as the previous filter, but also return @mateuszdabrowski.com, @mateuszdabrowski.sfmc.eu and other such domains.

All subscribers that have an email address ending with @mateuszdabrowski followed by a two-character top-level domain
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE EmailAddress LIKE '%@mateuszdabrowski.__'

In the marketing automation world, however, LIKE operator is especially useful for selecting contacts based on the multi picklist columns. Consider ActiveSubscriptions field that can have any combination of values Phone, TV, Internet.

Suppose you would like to select all contacts with active phone subscription. In that case, you shouldn't use equality operator (WHERE ActiveSubscription = 'Phone'), as it would return only contacts that have just this one subscription. Some of your customers might, however, have multiple (phone and tv or phone and internet plans). For SQL, a field containing 'Phone' is not equal with the one having a value of 'Phone', Internet'. LIKE operator is here to save the day.

All subscribers that have at least Phone subscription
SELECT
SubscriberKey,
EmailAddress
FROM MasterSubscriberDE
WHERE ActiveSubscription LIKE '%Phone%'
You Should Know

Always be sure to check whether you apply the wildcards correctly. Popular error is to have either too narrow or too wide scope of your filter. A common issue is lack of % operator in the end or beginning of the searched string. For your query, it means that the value should end there.

For example WHERE EmailAddress LIKE '%@gmail' should provide you zero results, as it would be looking for any address ending with @gmail - without any top-level domain like .com.

Logical operators

The WHERE statement supports standard logical operators - AND & OR.

They allow you to create more fine-tuned filters evaluating either multiple values or a single value from various points of view. You can use as many ANDs and ORs as you want, but keep in mind the AutoKill 30 minute limit for query execution.

All subscribers that have at least Phone subscription AND are in its twelfth month
SELECT
SubscriberKey,
EmailAddress
FROM MasterSubscriberDE
WHERE ActiveSubscription LIKE '%Phone%'
AND CONVERT(DATE, SubscriptionStart) >= DATEADD(MONTH, -12, CONVERT(DATE, GETUTCDATE()))
AND CONVERT(DATE, SubscriptionStart) < DATEADD(MONTH, -11, CONVERT(DATE, GETUTCDATE()))

The standard order of execution is evaluating the pairs joined by AND first and then considering the ones with OR. But you can use parentheses () to enforce your order:

All subscribers that have at least either Phone OR Internet subscription AND are in its twelfth month
SELECT
SubscriberKey,
EmailAddress
FROM MasterSubscriberDE
WHERE (ActiveSubscription LIKE '%Phone%' OR ActiveSubscription LIKE '%Internet%')
AND CONVERT(DATE, SubscriptionStart) >= DATEADD(MONTH, -12, CONVERT(DATE, GETUTCDATE()))
AND CONVERT(DATE, SubscriptionStart) < DATEADD(MONTH, -11, CONVERT(DATE, GETUTCDATE()))

There are many use cases for using AND or OR. The most popular ones are filtering by range (numerical or date-based) and capturing records fulfilling at least one of multiple criteria. For both, there is an optional shorthand.

BETWEEN shorthand

Whenever you want to work on ranges, instead of binding >= & <= scopes (closed range) with AND, you can leverage BETWEEN statement.

SELECT
SubscriberKey,
EmailAddress
FROM MasterSubscriberDE
WHERE (ActiveSubscription LIKE '%Phone%' OR ActiveSubscription LIKE '%Internet%')
AND CONVERT(DATE, SubscriptionStart)
BETWEEN DATEADD(MONTH, -12, CONVERT(DATE, GETUTCDATE()))
AND DATEADD(MONTH, -11, CONVERT(DATE, GETUTCDATE()))

The BETWEEN is replacing only a range greater or equal to the first value and smaller or equal to the second value. Additionally, it behaves weird when working with dates or datetime values.

For example: WHERE Date BETWEEN "01/01/2020" AND "31/01/2020" will work as if you wrote WHERE Date >= "01/01/2020 00:00:00" AND Date <= "31/01/2020 00:00:00". It would result in a lack of data for the last day of the range. In most cases you would rather want WHERE Date >= "01/01/2020 00:00:00" AND Date <= "31/01/2020 23:59:59".

Due to the above, I recommended to not use BETWEEN statement, unless you are sure the range is correct.

IN shorthand

There is another shorthand available - the IN statement. And it is much more interesting than the BETWEEN.

It allows you to simplify WHERE statement with multiple OR operators (like WHERE Status = 'held' OR Status = 'unsubscribed' OR Status = 'bounced') by using a list of searched values:

All subscribers with the unsubscribed, bounced or temporarily held status
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE Status IN ('held', 'unsubscribed', 'bounced')

As you can see above, the syntax is much shorter and more readable.

But the beauty of IN statement doesn't end there. It additionally optimizes the search speed by sorting the data and performing the binary search. Thanks to it, using IN instead of multiple ORs can lead to significant performance improvements.

You Should Know

Unfortunately, you cannot use IN statement together with LIKE statement. For such use case, you need to use one or more OR operators.

NOT operator

Sometimes you want to rather exclude some records instead of searching for them. In many cases, it can be done by just adjusting the operators (for example <> instead of =). There are, however, some situations where the NOT operator is the best tool for the job.

It is especially apparent when working with LIKE, BETWEEN, IN and EXISTS operators.

All subscribers NOT with unsubscribed, bounced or temporarily held status
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE Status NOT IN ('held', 'unsubscribed', 'bounced')
All subscribers that have email address NOT ending with @mateuszdabrowski.pl
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE EmailAddress NOT LIKE '%@mateuszdabrowski.pl'

CASE in WHERE

The CASE statement is not only useful in a SELECT part of the query. You can use it also within the WHERE. There are two approaches to do this.

The first compare 1 to the outcome of the CASE statement that evaluates each of its conditions to either 1 or 0. If the comparison evaluates to WHERE 1 = 1 it will resolve to TRUE and therefore return the result.

All subscribers NOT with unsubscribed, bounced or temporarily held status
SELECT
SubscriberKey,
EmailAddress
FROM _Subscribers
WHERE 1 = CASE Status
WHEN 'active' THEN 1
WHEN 'held' THEN 0
WHEN 'unsubscribed' THEN 0
WHEN 'bounced' THEN 0
END

It works, but in most cases, you can write it better with other means mentioned above. This query was previously written with single-line WHERE utilizing NOT IN statement.

The second approach compares a field to the CASE statement evaluation and is much more useful:

All subscribers that have EmailAddress different from the Preferred Email
SELECT
SubscriberKey,
EmailAddress
FROM MasterSubscriberDE
WHERE EmailAddress <> CASE
WHEN PreferredEmail = 'Personal Email' THEN PersonalEmail
WHEN PreferredEmail = 'Work Email' THEN WorkEmail
END

It still can be written using multiple AND & OR statements, but thanks to the CASE statement, it is shorter and more readable.

Last updated on by Mateusz Dąbrowski