Selected information on the SELECT statement in SFMC SQL
SELECT statement opens each query. It both defines the columns of data extensions and system data views that will be needed, as well as allows you to create entirely new data points.
The simplest version of this statement is
SELECT *, which targets all available columns from the source. I do not recommend this approach, as it is terrible for both readability and performance of your query. It is why Query Studio does not support this shortcut.
The prefered method is targeting columns explicitly by their names:
If a column name contains a space, you need to capture it with squared brackets:
There are also some exceptional cases related to selected System Data Views in Salesforce Marketing Cloud, where you need to use an underscore prefix before a name (for example when taking Subscriber ID from Enterprise Attributes):
You can also utilize
SELECT as a tool to rename the source columns to match your needs in an outcome data extension. It is especially useful when you are pulling the data from Synchronized Data Extension with names coming from Salesforce custom fields.
Use labels also for passing new values directly from your query. Need to add a custom string to outcome data extension? Or maybe synchronization timestamp for debugging purposes? You can leverage SQL for it.
You Should Know
You can even go further with creating new values by leveraging existing data! For example, if you have first name and last name, you can use SQL to create a FullName column from scratch:
This becomes truly powerful with the SQL functions.
CASE statement evaluates a list of conditions and returns one of the multiple possible result expressions. For Marketing Cloud purposes, it is especially useful when you not only want to select a column but also modify the values within it. Perfect for translating numeric codes to descriptive ones for personalization purposes. Excellent for standardizing data (change those
on values of opt-in to a single format).
You can also leverage labels to manipulate the column name at the same time:
Above examples are already useful in segmentation, but there is also one more way to write and use
CASE statement that gives even more power. The search
The difference is that you are not passing the column name on which you will be building conditions. Instead, you create a boolean expression from scratch for each scenario.
The example above doesn't show why it is worth to use the search
CASE. It is a bit longer than the standard approach! But there is a reason to use this form of
CASE statement that is visible once you want to create more elaborate conditions that are possible only with this version:
As you can see, thanks to search
CASE statement we were able to check conditions on multiple different columns and connect them with
OR statements for better control. You can use this version of the
CASE statement for all your needs (even those covered by the basic one) to worry not about two ways of formatting.
You Should Know
You can use
CASE statements inside a
CASE statement to have an even more dynamic query.
Additional elements that you will be using in many of your queries are table prefixes for selecting columns from multiple joined Data Extensions.
I'm covering this topic in-depth in
JOIN statement documentation.
Despite official SFMC SQL documentation states that it supports the
IF constructs, I was not able to make it work.
Variables & Functions
Unfortunately, SFMC SQL does not support variables.
Fortunately, it does support (selected) functions. Learn more about them in SFMC SQL Functions documentation.