Combine Data from Multiple Objects with Joins

Learning Objectives

After completing this unit, you’ll be able to:

• Describe the purpose and mechanics of a join query.
  
• Write a query to perform an INNER JOIN between two DMOs.
  
• Combine data using LEFT, RIGHT, and FULL OUTER JOINS.
  
• Ensure accurate join results by joining on both the ID and the key qualifier fields.
  

Explore the Use of Joins

So far, you’ve queried data from single data model objects (DMOs) and data lake objects (DLOs). But in Data 360, customer information is naturally spread across multiple objects. For example, a customer's profile is in the Individual DMO, while their email addresses are in the ContactPointEmail DMO.

To create a single, unified view of a customer, you need to use joins. A join combines data from two or more DMOs or DLOs based on a related field that they share. This allows you to bring together separate pieces of information, such as an individual's name and their corresponding email address, into one result set.

Inner Join

The INNER JOIN is the most common type of join. It returns only the rows that have matching values in the specified join fields in both tables. Records that only exist in one of the tables are excluded.

To perform an INNER JOIN:

• List the first table in the FROM clause.
  
• Use the INNER JOIN keyword, followed by the second table.
  
• Specify the join condition using the ON keyword.
  

To see the first name, last name, and email address for all individuals who have a recorded email contact point, use an INNER JOIN to ensure you only get records where both pieces of data exist.

In this query, the Individual DMO is joined to the ContactPointEmail DMO using the common key, ssot__IndividualId__c. Records are only included if the Individual ID exists in both DMOs. Notice that aliases (i. and e.) are used to clearly specify which table each field is coming from.

SELECT i."ssot__FirstName__c",
       i."ssot__LastName__c",
       e."ssot__EmailAddress__c"
FROM "ssot__Individual__dlm" i
INNER JOIN "ssot__ContactPointEmail__dlm" e
  ON i."ssot__Id__c" = e."ssot__IndividualId__c"

Outer Joins

While the INNER JOIN is useful for matching records, you sometimes need to include records that don't have a match in the other table. This is where outer joins come in.

LEFT OUTER JOIN

A LEFT OUTER JOIN (often shortened to LEFT JOIN) returns:

• All rows from the left table (the one listed first in the FROM clause).
  
• Any matching rows from the right table.
  
• If no match is found for a row in the left table, the fields from the right table are returned as NULL.
  

To get a full list of all individuals, including those who have no email address on file, this query is the same as the INNER JOIN, but using LEFT JOIN ensures all individuals are kept.

SELECT i."ssot__FirstName__c",
       i."ssot__LastName__c",
       e."ssot__EmailAddress__c"
FROM "ssot__Individual__dlm" i
LEFT JOIN "ssot__ContactPointEmail__dlm" e
  ON i."ssot__Id__c" = e."ssot__IndividualId__c"

RIGHT and FULL OUTER JOIN

• RIGHT OUTER JOIN (or RIGHT JOIN): Returns all rows from the right table, and the matched rows from the left table.
  
• FULL OUTER JOIN (or FULL JOIN): Returns all rows from both tables. Where there is no match, the columns from the non-matching side return NULL.
  

For simplicity and portability, LEFT JOIN is generally preferred. If you need a RIGHT JOIN, you can typically achieve the same result by swapping the order of the tables and using a LEFT JOIN.

Joining Best Practices: Use the Key Qualifier (KQ_Id__c)

In Unit 1, you learned about the key qualifier (KQ_Id__c) field. For most queries, especially those that involve one-to-many relationships (like one Individual having many Contact Points), Data 360 often requires you to join on both the primary ID and the KQ_Id__c for the most accurate and unique results.

A more robust join for your email list would look like this:

ON i."ssot__Id__c" = e."ssot__IndividualId__c"
  AND i."KQ_Id__c" = e."KQ_Id__c"

Joining on both fields is the best practice in Data 360 SQL to ensure you correctly link the specific, qualified version of a record.

You learned how to bring together complex data and filter, in the next unit you learn how to handle incorrect or mismatched data on the fly in your queries.

Practice what you learned by completing the challenge below before moving on to the next unit.