Different Types of Joins and Implementation

Introduction

In this blog, we would discuss what are Different Types of Joins in Python. There are a few different types of joins in Python, each with its own uses. Here, we’ll take a look at the most common types of joins and when you might want to use each one.

 

 

Inner Join: An inner join returns all rows from both tables that match the join conditions. This is the most common type of join and is typically what you want if you’re just looking to combine data from two tables.

 

 

Left Join: A left join returns all rows from the left table, even if there are no matching rows in the right table. This can be useful if you want to make sure you don’t lose any data from the left table, even if there is no corresponding data in the right table.

 

 

Right, Join: A right join is similar to a left join, but it returns all rows from the right table, even if there are no matching rows in the left table. This can be useful if you want to make sure you don’t lose any data from the right table, even if there is no corresponding data in the left table.

 

 

Full Join: A full join returns all rows from both tables, even if there are no matching rows in either table. This can be useful if you want to make sure you don’t lose any data from either table, even if there is no corresponding data in the other table.

 

 

 

Implementation of Different joins

 

import pandas as pd
import numpy as np
 
# data frame 1
d1 = {'Product_id':pd.Series([1,2,3,4,5,6]),
  'Product name':pd.Series(['Watch','Glasses','Watch','Belt','Glasses','Belt'])}
df1 = pd.DataFrame(d1)
 
 
# data frame 2
d2 = {'Product_id':pd.Series([1,3,5,7,8]),
    'brand':pd.Series(['Titan','Titan','Ray-Ban','Sony','Samsung'])}
df2 = pd.DataFrame(d2)

Output:

 

 

 

Inner Join: An inner join is the most common type of join, and is used to combine data sets based on a common key. Inner joins return all rows from both data sets that match the key and can be used to effectively combine data from two or more tables.

 

#inner join in python pandas
 
inner_join_df= pd.merge(df1, df2, on='Product_id', how='inner')
inner_join_df

Output:

 

 

 

Outer Join: An outer join is used to combine data sets where the key may not be present in both data sets. Outer joins return all rows from both data sets, including those rows that do not match the key. This type of join is often used to fill in missing data or to combine data sets that are not perfectly matched.

 

# outer join in python pandas
 
outer_join_df=pd.merge(df1, df2, on='Product_id', how='outer')
outer_join_df

Output:

 

 

 

Left Join: A left join is similar to an outer join, but only returns rows from the first data set that match the key. This type of join is often used when the first data set is known to be complete, and the second data set is used to fill in missing data.

 

# left join in python
 
left_join_df= pd.merge(df1, df2, on='Product_id', how='left')
left_join_df

Output:

 

 

 

Right Join: A right join is similar to a left join, but returns rows from the second data set that match the key. This type of join is often used when the second data set is known to be complete, and the first data set is used to fill in missing data.

 

# right join in python pandas
 
right_join_df= pd.merge(df1, df2, on='Product_id', how='right')
right_join_df

Output:

 

 

 

 

Also, read – What is Gated recurrent unit and its working