{"id":22760,"date":"2023-09-21T10:00:00","date_gmt":"2023-09-21T08:00:00","guid":{"rendered":"https:\/\/www.marketinet.com\/blog\/sql-joins-in-salesforce-marketing-cloud\/"},"modified":"2025-04-07T10:07:22","modified_gmt":"2025-04-07T08:07:22","slug":"sql-joins-in-salesforce-marketing-cloud","status":"publish","type":"post","link":"https:\/\/www.marketinet.com\/en\/blog\/sql-joins-in-salesforce-marketing-cloud\/","title":{"rendered":"The Power of SQL Join Tables in SalesForce Marketing Cloud"},"content":{"rendered":"

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.<\/span><\/p>\n

<\/p>\n

\u00a0<\/p>\n

In future articles we will talk about DataViews<\/a>, 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.<\/span><\/p>\n

\u00a0<\/p>\n

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.<\/span><\/p>\n

\u00a0<\/p>\n

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.<\/span><\/p>\n

\u00a0<\/p>\n

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.<\/span><\/p>\n

\u00a0<\/p>\n

TYPE 1: Left Outer Join\u00a0<\/h2>\n

\u00a0<\/p>\n

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.<\/p>\n

\u00a0<\/p>\n

\"Left<\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong> A.name, B.category, B.date, B.amount<\/span><\/p>\n

FROM<\/span><\/strong>\u00a0<\/span>user<\/span>\u00a0<\/span>A<\/span>\u00a0<\/span><\/p>\n

LEFT<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user <\/span>B<\/span>\u00a0<\/span><\/p>\n

ON<\/span><\/strong>\u00a0<\/span>A<\/span>.<\/span>id_user<\/span>\u00a0<\/span>=<\/span>\u00a0<\/span>B<\/span>.<\/span>id_user<\/span>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

\"Table<\/p>\n

\u00a0<\/p>\n

TYPE 2: Left Outer Join with Exclusions<\/h2>\n

\u00a0<\/p>\n

\"Left<\/p>\n

\u00a0<\/p>\n

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:<\/span><\/span><\/p>\n

SELECT<\/span><\/strong>\u00a0<\/span>A<\/span>.<\/span>name<\/span>,<\/span> B.category, B.date, B.amount<\/span><\/p>\n

FROM<\/span><\/strong> user <\/span>A<\/span>\u00a0<\/span><\/p>\n

LEFT<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user B<\/span>\u00a0<\/span><\/p>\n

ON<\/span><\/strong>\u00a0<\/span>A<\/span>.user_id = B.user_id<\/span>\u00a0<\/span><\/p>\n

WHERE<\/span><\/strong>\u00a0<\/span><\/p>\n

B<\/span>.user_id <\/span>IS<\/span><\/strong>\u00a0<\/span>NULL<\/span><\/strong>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

And the result is in this table\u2026<\/p>\n

\u00a0<\/p>\n

\"Tabla<\/span><\/p>\n

\u00a0<\/p>\n

{{cta(‘07552fae-6d5c-4f5c-9eef-393170a82bef’,’justifycenter’)}}<\/p>\n

\u00a0<\/p>\n

TYPE 3: Right Outer Join\u00a0<\/span><\/h2>\n

\u00a0<\/p>\n

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.):<\/span><\/span><\/p>\n

\"Right<\/span><\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong>\u00a0<\/span>A<\/span>.<\/span>name<\/span>,<\/span> B.category, B.date, B.amount<\/span>\u00a0<\/span><\/p>\n

FROM<\/span><\/strong> user <\/span>A<\/span>\u00a0<\/span><\/p>\n

RIGHT<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user <\/span>B<\/span>\u00a0<\/span><\/p>\n

ON<\/span><\/strong> A.user_id = B.user_id<\/span>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

\"Tabla<\/span><\/p>\n

\u00a0<\/p>\n

TYPE 4: Right Outer Join with exclusions<\/span><\/h2>\n

\u00a0<\/p>\n

With this type, we would remove all \u201corphan\u201d purchases.<\/p>\n

\u00a0<\/p>\n

\"Right<\/span><\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong> A.name, B.category, B.date, B.amount<\/span>\u00a0<\/span><\/p>\n

FROM<\/span><\/strong> user <\/span>A<\/span>\u00a0<\/span><\/p>\n

RIGHT<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase B_user<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

ON<\/span><\/strong> A.user_id = B.user_id<\/span>\u00a0<\/span><\/p>\n

WHERE<\/span><\/strong>\u00a0
<\/span>A.user_id <\/span>IS<\/span><\/strong>\u00a0<\/span>NULL<\/span><\/strong>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

\"Tabla<\/span><\/p>\n

\u00a0<\/p>\n

TYPE 5: Inner Join\u00a0<\/span><\/h2>\n

\u00a0<\/p>\n

We would almost say that this type is the classic one, <\/strong>it is the pure intersection of sets and only extracts the matching ones between both tables.<\/strong><\/span><\/p>\n

\"Inner<\/span><\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong> A.name, B.category, B.date, B.amount<\/span>\u00a0<\/span><\/p>\n

FROM<\/span><\/strong> user<\/span>\u00a0<\/span>A<\/span>\u00a0<\/span><\/p>\n

INNER<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user B<\/span>\u00a0<\/span><\/p>\n

ON<\/span><\/strong> A.user_id = B.user_id<\/span>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

\"Table<\/span><\/p>\n

\u00a0<\/p>\n

TYPE 6: Full Outer Join<\/span><\/h2>\n

\u00a0<\/p>\n

With full Outer join,<\/strong> 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.<\/strong><\/span><\/p>\n

\u00a0<\/p>\n

\"Full<\/span><\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong> A.name, B.category, B.date, B.amount<\/span>\u00a0<\/span><\/p>\n

FROM<\/span><\/strong> user A<\/span>\u00a0<\/span><\/p>\n

FULL<\/span><\/strong>\u00a0<\/span>OUTER<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user B<\/span><\/p>\n

ON<\/span><\/strong> A.user_id = B.user_id<\/span><\/p>\n

\u00a0<\/p>\n

\"Tabla<\/span><\/p>\n

\u00a0<\/p>\n

TYPE 7: Outer Join<\/span><\/h2>\n

In the latter case we would remove what we have said were the \u201corphan\u201d purchases and the users who have not purchased.<\/strong><\/span><\/p>\n

\u00a0<\/p>\n

\"Outer<\/span><\/p>\n

\u00a0<\/p>\n

SELECT<\/span><\/strong> A.name, B.category,B.date,B.amount<\/span><\/p>\n

FROM<\/span><\/strong> user A<\/span><\/p>\n

FULL<\/span><\/strong>\u00a0<\/span>OUTER<\/span><\/strong>\u00a0<\/span>JOIN<\/span><\/strong> purchase_user B<\/span><\/p>\n

ON<\/span><\/strong> A.user_id = B.user_id<\/span><\/p>\n

WHERE<\/span><\/strong>\u00a0<\/span><\/p>\n

A.user_id <\/span>IS<\/span><\/strong>\u00a0<\/span>NULL<\/span><\/strong>\u00a0<\/span><\/p>\n

OR<\/span><\/strong>\u00a0<\/span><\/p>\n

B.user_id <\/span>IS<\/span><\/strong>\u00a0<\/span>NULL<\/span><\/strong>\u00a0<\/span><\/p>\n

\u00a0<\/p>\n

\"Table<\/span><\/p>\n

\u00a0<\/p>\n

CONCLUSION<\/strong><\/h2>\n

\u00a0<\/p>\n

If you want to get the most out of Marketing Cloud and the email marketing strategy,<\/a> 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.<\/p>\n

\u00a0<\/p>\n

{{cta(’52fa9c99-860e-4fbd-b8e0-8084060f20e8′,’justifycenter’)}}<\/strong><\/p>\n

\u00a0<\/p>\n

\n

If you liked this article, you might also be interested in:<\/p>\n

\u00a0<\/p>\n