Salesforce Marketing Cloud - SQL Features & Tips Part 2

SQL in Salesforce Marketing Cloud — No mistakes, but still wrong results?

Case 2: Be careful when using UNION

The UNION function is used to merge data with a similar structure from two or more data extensions into a data table. Here is an example of a common query:

SELECT EmailAddress,
	SubscriberKey,  
	FirstName,
	LastName
	FROM [DataExtension1]
UNION
SELECT EmailAddress,   
	SubscriberKey,   
	FirstName,   
	LastName
	FROM [DataExtension2]

This query combines the fields from DataExtension1 and DataExtension2 into a new data extension. The UNION function automatically removes duplicate data records. However, there is also a pitfall to consider here: The number and order of fields in all UNION SQL queries must match.

Correctly handle different numbers of fields in data extensions for SQL queries

The solution is fairly simple. If a field is missing in one of the data extensions, you should either set a default value or specify that the field should remain empty. It is important that you do not simply omit a missing field such as firstName in the second data extension, as this will result in an error message. We will use an example to explain:

Let's assume that Data Extension 1 is structured as follows:

emailAddress | subscriberKey | firstName | lastName
And dataExtension2 has the fields in this order:

SubscriberKey | EmailAddress | LastName

You now want to merge the data from both data extensions and save it to DataExtension3, which has the same field layout as DataExtension1. To ensure that there are no issues, you should use the following query:

SELECT EmailAddress,
	SubscriberKey,
	FirstName,
    LastName
	FROM [DataExtension1]
UNION
SELECT EmailAddress,
	SubscriberKey,
	FirstName=NULL,
	LastName,
	FROM [DataExtension2]

Your result in Data Extension 3 will be as follows:

Why is the order of fields so important in UNION queries?

Another special feature of using UNION is the order of the fields queried. This order must be exactly the same in all queries that belong to the UNION. Otherwise, the results won't be merged as desired, even though no error message is displayed. It is important to know that the order of the fields in the first query before the UNION command determines the order in which the fields are written to the target data extension. This order then remains the same for all subsequent queries in the UNION. We also have a small example of this:

For example, if you copy the order from the data extensions, but it is different, as in this example:

SELECT EmailAddress,
	SubscriberKey,
	FirstName,
	LastName
	FROM [DataExtension1]

UNION

SELECT SubscriberKey,   
	EmailAddress,   
	LastName,   
	FirstName = NULL

Then your result in Data Extension 3 would look like this:

Therefore, make sure that the fields are queried in the same order in all queries to achieve the desired result.

UNION versus UNION ALL

For the sake of completeness, we would like to add the difference between UNION and UNION ALL. Both methods combine results from multiple queries. The key difference, however, is that, as already mentioned, UNION removes duplicate results, while UNION ALL shows all values, including duplicates.

We know that SQL isn't always easy to use in Salesforce Marketing Cloud. It often comes down to small but decisive details, such as the correct time zone or the correct sequence of queries. These subtleties can make the difference between successful and incorrect data processing. Should you encounter any problems or need assistance, do not hesitate to contact us as an expert. We are always happy to help you with challenges with SQL or other topics related to Salesforce Marketing Cloud.