Guide to SQL joins: Intuition and Implementation

Introduction

The purpose of a join clause is  to combine rows from different tables, based on a related column between them which is known as the “Key”.

The various types joins can be intuitively understood with the help of Venn diagrams mentioned below, we will be talking about each of these joins separately and we will also see how to implement them  using SQL.

Types_of_Joins

Types_of_Joins

The dataset that has been used to illustrate the examples in this article is publicly available and can be downloaded from this link

The below image gives us an un idea about the tables that we will be using in this article:

Table Details

Table Details

Left Inclusive

The first join that we will be talking is the Left inclusive join, here our final goal is to obtain a table that contains all the records from the left table mapped to the corresponding row for the right table.

Left_Inclusive_Output

Left_Inclusive_Output

Let us check out the number of records in this table:

Left_Inclusive_Count

 

 

As you can notice above that there are certain nulls in the Age column, these are those records that are present in the left table but not in the right table. Also the count of records in the final table is 113 which is the same as our left table.

Left Exclusive

In the Left exclusive join, our final goal is to obtain a table that contains all the records that are present in the left table but not in the right table.

Left_Exclusive_Output

Left_Exclusive_Output

Let us check out the number of records in this table:

Left_Exclusive_Count

 

 

As we can notice the output that all the rows in the Age column are null, all these records are present in the left table but not in the right table. Also the count of records in the final table is 20 which means that the left table has 20 records that are not present in the right table.

Full Outer Inclusive

In the Full Outer inclusive join, our final goal is to obtain a table that contains all the records that are present in the both the tables as is represented in the Venn diagram.

Full_Outer_Exclusive_Output

Full_Outer_Inclusive_Output

Let us check out the number of records in this table:

Full_Outer_Inc

 

 

We can conclude from the above results that in total there are 125 distinct Customer_ID’s available in both these datasets.

Inner Join

We perform the inner join to obtain the records that are common to both the left and the right table.

Inner_Join_Output

Inner_Join_Output

Count of records:

Inner_join_counts

 

 

We can conclude from the above results that both the tables contain 93 records common, we can also notice that there are no nulls in the State and Age columns as these Customer_IDs are present in both the tables.

Right Inclusive

Right Inclusive join is similar to the left inclusive join here the only difference is that the final output must contain all the records of the right table and the corresponding row from the left table.

Right_Inclusive_Output

Right_Inclusive_Output

Right_Inclusive_Counts

 

 

We can conclude from the outputs that there are a total of 105 Customer_IDs in the right table.

Right Exclusive Join

Our main goal while performing the right exclusive file is to obtain a table that contains all the records that are present in the right table but not in the left table.

Right_Exclusive_Output

Right_Exclusive_Output

Counts:

Right_Exclusive_Count

 

 

We can conclude from the above results that there are 12 Customer_IDs that are present in the right table but not in the left table.

Full Outer Exclusive

The full Outer Exclusive join is performed to obtain all the records from both the tables excluding the common records as is illustrated in the Venn Diagram.

Full_Outer_Exclusive_Output

Full_Outer_Exclusive_Output

Full_Outer_Exclusive_Output

 

The above results tell us that there are 32 records present across both the tables that are not common.

We have now successfully seen how and when to use the SQL joins and this knowledge can now be applied to real-world data.

You may also like...