Use WHERE to make your query lean and to the point. Less is more.
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.
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).
Comparison operators (
<=) are perfect when you are operating on numbers (for example selecting contacts with low Customer Lifetime Value) or dates.
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.
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).
LIKE operator, you need to leverage at least one of the two available wildcards:
|Zero, one or more characters|
|Exactly one character|
For example, you can search for
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.sfmc.eu and other such domains.
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
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
LIKE operator is here to save the day.
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.
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
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.
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:
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
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 to 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:
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
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
CASE in WHERE
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 therefore return the result.
It works, but in most cases, you can write it better with other means mentioned above. This query was previously written with single-line
NOT IN statement.
The second approach compares a field to the
CASE statement evaluation and is much more useful:
It still can be written using multiple
OR statements, but thanks to the
CASE statement, it is shorter and more readable.