SQL_Joins

                                                  

                                JOINS

JOIN in Salesforce Marketing Cloud

In SFMC, a JOIN is used to combine data from multiple Data Extensions (tables) using a common field.

In Salesforce Marketing Cloud, data is usually stored in multiple Data Extensions (DEs) instead of one single table.

JOINs are important because they help combine related data from different DEs using a common field.

---

Why JOINs Are Important

Without JOINs:

👉

1. data remains separated
2. reports become difficult
3. personalization is limited
4. segmentation becomes hard

JOINs help marketers connect subscriber data, order data, journey data, and engagement data together.

First of all we created Three Source tables and one Target Table 

1. Push_Order_Table_Joins

2. Push_Customer_Table_Joins

3. Push_selfJoin

4. Push_Customer_Table_Joins_Target

-----------------------------------------------------------------------------------------------------------------------------

Inner Join :  

 INNER JOIN returns only the rows that have matching values in both tables

Query:  

 

Select CDE.CustomerID, CDE.Name, CDE.City, ODE.Product from Push_Customer_Table_Joins CDE  inner join Push_Order_Table_Joins ODE  on CDE.CustomerID = ODE.CustomerID

 

Result : Inner Join only return common or matching data from both the table.

Left Join :

LEFT JOIN returns:

👉

• all records from the left table
• matching records from the right table

If no match exists, NULL values are returned from the right table.

In SFMC :  

LEFT JOIN shows all data from the left Data Extension and matching data from the right Data Extension.

Left Table 

Right Table  

Query :

select CDE.CustomerID, CDE.Name, CDE.City, ODE.Product

from Push_Customer_Table_Joins CDE

Left join Push_Order_Table_Joins ODE

on CDE.CustomerID = ODE.CustomerID

Result :      In result, all the left Table data is fetched and only matching data from right table fetch, Where data is not available for particular CustomerID - data is null. 

Right Join:   




RIGHT JOIN returns:

👉

• all records from the right table
• matching records from the left table

If no match exists, NULL values are returned from the left table. 

In SFMC: 

RIGHT JOIN shows all data from the right Data Extension and matching data from the left Data Extension.

Left Table 

Right Table  

Query :

select CDE.CustomerID, CDE.Name, CDE.City, ODE.Product

from Push_Customer_Table_Joins CDE

Right join Push_Order_Table_Joins ODE

on CDE.CustomerID = ODE.CustomerID

Result :