Use WHERE to make your query lean and to the point. Less is more.
Table of Contents
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
WHERE will filter it out.
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE Domain = 'mateuszdabrowski.pl'
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.
|Not equal to|
|Greater or equal to|
|Less or equal to|
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).
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE Status != 'unsubscribed'
Comparison operators (
<=) are perfect when you are operating on numbers (for example selecting contacts with low Customer Lifetime Value) or dates.
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE 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.
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 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
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
LIKE operator is here to save the day.
SELECT SubscriberKey , EmailAddressFROM MasterSubscriberDEWHERE 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
WHERE statement supports standard logical operators -
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
ORs as you want, but keep in mind the AutoKill 30 minute limit for query execution.
SELECT SubscriberKey , EmailAddressFROM MasterSubscriberDEWHERE 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:
SELECT SubscriberKey , EmailAddressFROM MasterSubscriberDEWHERE (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
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.
Whenever you want to work on ranges, instead of binding
<= scopes (closed range) with
AND, you can leverage
SELECT SubscriberKey , EmailAddressFROM MasterSubscriberDEWHERE (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()))
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.
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.
There is another shorthand available - the
IN statement. And it is much more interesting than the
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:
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE 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.
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
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE Status NOT IN ('held', 'unsubscribed', 'bounced')
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE EmailAddress NOT LIKE '%@mateuszdabrowski.pl'
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
0. If the comparison evaluates to
WHERE 1 = 1 it will resolve to
TRUE and return the result.
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE 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
NOT IN statement.
You Should Know
You can do better even with the search version of
CASE, as it allows you to use
IN within the
SELECT SubscriberKey , EmailAddressFROM _SubscribersWHERE 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
The second approach compares a field to the
CASE statement evaluation and is much more useful:
SELECT SubscriberKey , EmailAddressFROM MasterSubscriberDEWHERE EmailAddress != CASE WHEN PreferredEmail = 'Personal Email' THEN PersonalEmail WHEN PreferredEmail = 'Work Email' THEN WorkEmail END
It can still be written using multiple
OR statements, but it is shorter and more readable thanks to the
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.