None of what we've discussed so far about using SQL QUERY instead of filtered Data Extensions would make sense, if you didn't want to get the most out of it by joining tables. In Marketing Cloud you can join your Data Extensions with others through relationships. In addition, you have the DataViews (very interesting information for segmentation) and even new tables generated by Einstein.
In future articles we will talk about DataViews, because they allow you to do things like sending an email to those who opened a previous email or sending it even to those who did not open it, among many other options.
Now we are going to focus on making queries between several tables, what we call JOINS. In this case, we will rely on 2 tables, but this can be made as complicated as you want with more tables and even different JOIN types.
To explain it, it is best to use Venn diagrams like we did in school, so you will understand it better. In this case, we are going to see the 7 main JOINs that are used. There are some more, but they are unusual.
For this example, we are going to use 2 tables, user and purchase. From the user table, we will get the name, from the purchase table: category, date and amount. Both tables are related to the user_id field.
TYPE 1: Left Outer Join
In this first option we want all the data from the user table, and all those that are related to the purchases table. That is, all users, whether they have purchased or not.
SELECT A.name, B.category, B.date, B.amount
FROM user A
LEFT JOIN purchase_user B
ON A.id_user = B.id_user
TYPE 2: Left Outer Join with Exclusions
With this JOIN what we get is all the users, except those who match the purchase. In other words, we would be removing all the users who have NOT purchased. Useful, right? The SQL would be:
SELECT A.name, B.category, B.date, B.amount
FROM user A
LEFT JOIN purchase_user B
ON A.user_id = B.user_id
WHERE
B.user_id IS NULL
And the result is in this table…
TYPE 3: Right Outer Join
In this option we want all the data from the purchases table and all those that are related to the users table. In theory, this should match in its entirety, but in this case we have forced it not to be like this as if some purchase were not related to any user (sometimes it is done for units given up for lost, etc.):
SELECT A.name, B.category, B.date, B.amount
FROM user A
RIGHT JOIN purchase_user B
ON A.user_id = B.user_id
TYPE 4: Right Outer Join with exclusions
With this type, we would remove all “orphan” purchases.
SELECT A.name, B.category, B.date, B.amount
FROM user A
RIGHT JOIN purchase B_user
ON A.user_id = B.user_id
WHERE
A.user_id IS NULL
TYPE 5: Inner Join
We would almost say that this type is the classic one, it is the pure intersection of sets and only extracts the matching ones between both tables.
SELECT A.name, B.category, B.date, B.amount
FROM user A
INNER JOIN purchase_user B
ON A.user_id = B.user_id
TYPE 6: Full Outer Join
With full Outer join, as the word FULL indicates, we would get everything, users whether or not they match purchases, purchases even if they do not match users and the intersection of both.
SELECT A.name, B.category, B.date, B.amount
FROM user A
FULL OUTER JOIN purchase_user B
ON A.user_id = B.user_id
TYPE 7: Outer Join
In the latter case we would remove what we have said were the “orphan” purchases and the users who have not purchased.
SELECT A.name, B.category,B.date,B.amount
FROM user A
FULL OUTER JOIN purchase_user B
ON A.user_id = B.user_id
WHERE
A.user_id IS NULL
OR
B.user_id IS NULL
CONCLUSION
If you want to get the most out of Marketing Cloud and the email marketing strategy, which is ultimately the objective, the essential thing is to know how to correctly segment our audiences. To make these segmentations correctly it is important to be able to combine several tables. This is where JOINs come into play, something that you will have to master in order to get the most out of your segmentations through SQL QUERY.
If you liked this article, you might also be interested in:
