Joins

Data Manipulation in Alteryx

Behrang Behjoo

Business Intelligence (BI) Leader

Joins

Joins are essential when working with relational data

Relational data model

Data Manipulation in Alteryx

Mechanics of joins

$$

Related key fields that exists in both tables:

  • Primary key
  • Foreign key

Two tables combined into one by keys

Data Manipulation in Alteryx

Types of joins

Inner join Inner join venn diagram

Data Manipulation in Alteryx

Types of joins

Inner join Inner join venn diagram

Left join Left join venn diagram

Data Manipulation in Alteryx

Types of joins

Inner join Inner join venn diagram

Left join Left join venn diagram

Right join Right join venn diagram

Data Manipulation in Alteryx

Types of joins

Inner join Inner join venn diagram

Left join Left join venn diagram

Right join Right join venn diagram

Full join Full join venn diagram

Data Manipulation in Alteryx

Example from Velocity Inc. dataset

left join example before.jpg

Data Manipulation in Alteryx

Example from Velocity Inc. dataset

left join example after.jpg

Data Manipulation in Alteryx

Example from Velocity Inc. dataset

inner join example before.jpg

Data Manipulation in Alteryx

Example from Velocity Inc. dataset

inner join example after.jpg

Data Manipulation in Alteryx

Cross join

  • Produces Cartesian product
  • Good for all possible combinations between two sets of data

Cross join venn diagram.png

Data Manipulation in Alteryx

An example of a Cross join

two tables fruit and color.png

Data Manipulation in Alteryx

An example of a Cross join

cross_join_of the fruit and color tables.png

Data Manipulation in Alteryx

Common mistakes to avoid with joins

$$$$$$

  • Lacking precise detail in data representation
  • Forgetting to specify join type
  • Ignoring null values
  • Mismatched data types

data analyst investigating sketch

Data Manipulation in Alteryx

Let's practice!

Data Manipulation in Alteryx

Preparing Video For Download...