SFMC SQL Select

Selected information on the SELECT statement in SFMC SQL

The 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:

SELECT
ContactKey,
EmailAddres

If a column name contains a space, you need to capture it with squared brackets:

SELECT
ContactKey,
EmailAddres,
[External Contact Id]

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):

SELECT
_SubscriberId

Labels

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.

SELECT
ContactKey,
EmailAddress,
[External Contact Id] AS ExternalContactId,
Gender__c AS Gender

New values

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.

Add custom column labaled 'ImportDate' with value equal to the UTC time of the script execution for each record
SELECT
ContactKey,
EmailAddress,
[External Contact Id] AS ExternalContactId,
Gender__c AS Gender,
'Lead' AS RecordType,
GETUTCDATE() AS ImportDate
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:

SELECT
SubscriberKey,
EmailAddress,
FirstName + ' ' + LastName AS FullName

This becomes truly powerful with the SQL functions.

Conditions

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 1, true and on values of opt-in to a single format).

SELECT
ContactKey,
EmailAddress,
[External Contact Id] AS ExternalContactId,
Gender__c AS Gender,
'Lead' AS RecordType,
GETUTCDATE() AS ImportDate,
CASE MemberType__c
WHEN 1 THEN 'Bronze'
WHEN 2 THEN 'Silver'
WHEN 3 THEN 'Gold'
WHEN 4 THEN 'Platinum'
ELSE 'Unregistered'
END

You can also leverage labels to manipulate the column name at the same time:

SELECT
ContactKey,
EmailAddress,
[External Contact Id] AS ExternalContactId,
Gender__c AS Gender,
'Lead' AS RecordType,
GETUTCDATE() AS ImportDate,
CASE MemberType__c
WHEN 1 THEN 'Bronze'
WHEN 2 THEN 'Silver'
WHEN 3 THEN 'Gold'
WHEN 4 THEN 'Platinum'
ELSE 'Unregistered'
END AS MemberType

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 CASE.

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 same Query as the previous one, but written with Search Case
SELECT
ContactKey,
EmailAddress,
[External Contact Id] AS ExternalContactId,
Gender__c AS Gender,
'Lead' AS RecordType,
GETUTCDATE() AS ImportDate,
CASE
WHEN MemberType__c = 1 THEN 'Bronze'
WHEN MemberType__c = 2 THEN 'Silver'
WHEN MemberType__c = 3 THEN 'Gold'
WHEN MemberType__c = 4 THEN 'Platinum'
ELSE 'Unregistered'
END AS MemberType

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:

Lets calculate what channel we should use in our campaign for a given user
SELECT
ContactKey,
CASE
WHEN IsAppInstalled = 1 THEN 'Push'
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
WHEN PhoneOptIn = 1 AND Mobile IS NOT NULL THEN 'Phone'
END AS PrefferedChannel

As you can see, thanks to search CASE statement we were able to check conditions on multiple different columns and connect them with AND/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.

Let's add another dimension of membership level to differentiate available channels
SELECT
ContactKey,
CASE
WHEN MemberType = 'Platinum' THEN 'Key Account Manager'
WHEN MemberType = 'Gold' OR MemberType = 'Silver' THEN
CASE
WHEN PhoneOptIn = 1 AND Mobile IS NOT NULL THEN 'Phone'
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
WHEN IsAppInstalled = 1 THEN 'Push'
END
WHEN MemberType = 'Bronze' THEN
CASE
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
WHEN IsAppInstalled = 1 THEN 'Push'
END
END AS PrefferedChannel

Table Prefixes

Additional elements that you will be using in many of your queries are table prefixes for selecting columns from multiple joined Data Extensions.

SELECT
_Subscriber.SubscriberKey
_EnterpriseAttribute.LastName

I'm covering this topic in-depth in JOIN statement documentation.

Flow control

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.

Last updated on by Mateusz Dąbrowski