10.8. Full Outer Join in Looker Studio

A full outer join is the opposite of an inner join. While an inner join drops any non-matching rows, a full outer join keeps everything, even if they don't match. In a full outer join, you don't want to lose any data, and for anything that cannot be matched, a null value will be returned. In the next lesson, we will discover how to use this feature of Full Outer Join to Union two data sources In looker Studio.

Full Outer Join Example

Suppose you have two tables with date, customer revenue, and ad costs. You want to perform a full outer join to keep all values from both tables. To do this in Looker Studio, configure the full outer join by selecting the appropriate join condition and join operator. In this case, you're joining on dates.

The resulting join will show all the dates, revenues, and ad costs from both tables. However, the table might not be in the format you desire, with duplicate date columns and null values.

To clean up the table, you can use a function called coalesce. This function returns the first non-missing value found in the list of fields. By applying coalesce to the date columns, you can create a single date column containing the actual date from either the left or right table.

Now, with the cleaned-up table, you can see the dates, revenues, and ad costs from both tables, even when some values are missing. Depending on the context, you might decide to keep null values or replace them with zeros and proper formatting.

Additional real-life example: In a business scenario, you might have two separate datasets from different departments in your company. One dataset contains sales data, and the other dataset includes marketing campaign data. By performing a full outer join, you can keep all the data from both datasets while connecting them on a common attribute, such as the date or campaign ID.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn