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 your queries' outcomes.

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, WHERE will filter it out.

All subscribers with domain equal to 'mateuszdabrowski.pl'

SELECT
      SubscriberKey
    , EmailAddress
FROM _Subscribers
WHERE Domain = 'mateuszdabrowski.pl'

You Should Know

WHERE statement is heavily impacted by NULL values - be sure to check dedicated guide on how to solve those issues.

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.

Operator	Description
=	Equal to
!= or <>	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 a simple date (day, month, year without the time). We are also getting a current date in UTC format and converting it to a simple date. Next, we subtract one month and compare it to the DateJoined transformed value. I cover more such examples in the SFMC SQL Date Functions.

LIKE operator

Another handy operator is LIKE. It allows you to leverage wildcards to search for matching records based on value fragment. You may know it from the SELECT CASE statement, but it is much more useful with WHERE. Think about filtering with just a part of the value (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).

However, in the marketing automation world, the LIKE operator is handy for selecting contacts based on the multi-picklist columns. Consider ActiveSubscriptions field with any combination of values Phone, TV, or 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. However, some of your customers might have multiple (phone and tv or phone and internet plans). For SQL, a field containing 'Phone' is not equal to 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%'

It is also an excellent tool for database cleaning by finding incorrect values within automation. To learn more about the possibilities, check out the LIKE guide.

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 evaluates the pairs joined by AND first and then considers 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 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. You need to use one or more OR operators for such use case.

NOT operator

Sometimes you want to rather exclude some records instead of searching for them. In many cases, you can do it 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 operator

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 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 you can write it better with other means mentioned above in most cases. This query was previously coded with single-line WHERE utilizing NOT IN statement.

You Should Know

You can do better even with the search version of CASE, as it allows you to use AND/OR/IN within the CASE:

All subscribers NOT with unsubscribed, bounced or temporarily held status

SELECT
     SubscriberKey
   , EmailAddress
FROM _Subscribers
WHERE
   1 = CASE
       WHEN Status = 'active'                              THEN 1
       WHEN Status IN ('held', 'unsubscribed', 'bounced')  THEN 0
   END

Notice the lack of Status column name right after CASE keyword and its addition in each WHEN.

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 can still be written using multiple AND & OR statements, but it is shorter and more readable thanks to the CASE statement.

You Should Know

CASE statement returns THEN value of the first condition that evaluated to TRUE. It means that if in your query first condition is true, the rest will be ignored. Even if for the selected contact, all of them would be true.

Always consider the order of the conditions in your CASE statement. It might lead to errors if you don't expect behaviour mentioned above, and make your life much easier if you know it works that way.

To learn more about the possibilities and shorthands, check out the CASE guide.