Spark Join Types With Examples

Updated On March 31, 2021 | By Mahesh Mogal

When we are dealing with a lot of data coming from different sources, joining two or more datasets to get required information is a common use case. So it is a good thing Spark supports multiple join types. In this blog, we will learn spark join types with examples.

Spark Join Types

Like SQL, there are varaity of join typps available in spark.

  • Inner Join - Keeps data from left and right data frame where keys exist in both
  • Outer join - keeps data from left and right data frame where keys exist in either left or right data frame
  • Let outer join - keeps data with keys in left data frame
  • Right outer join - keeps data with keys in right data frame
  • Left semi join - Only gets data from left data frame for which we have matching key in right data frame
  • Left anti join - Only gets data from right data frame for which we do not have any matching key in right data frame
  • Natural join - Joins two data frames with same column names
  • Cross Join - joins every row from left data frame with every other row in right data frame

Now that we know all spark join types let us learn these using code and example data to understand them in a better way.

Setting Up Data

Before we start, we will need data frames on which we can test join types. Instead of reading lot of data, in this case we will set up small data frames which we can easily validate.

We have set up two simple data frames, one with employees and one for departments. We have department id as a common column between these two. Now this is set up let us start with spark joins.

Inner Join

Inner join returns data from left and right data frame where join key is present in both data frames. For example, it will return data from employee and department data frame where same department id is present in both of them. Let us see that in example.

We can also format this code for better understanding by making join condition and join type as separate variables.

By default, spark performs inner join. So when we want to do inner join we can skip join type parameter and we will still receive same result.

Outer Join

In case of outer joins, Spark will take data from both data frames. If the matching key is not present in left or right data frame, Spark will put "null" for that data.

We can see that spark has picked all rows from both data frames and where it did not find a matching key like department id "D1022" it has inserted null for data coming from departments.

Left Outer Join

Spark will pick all rows from left data frame. For keys matching from the right data frame, it will get that data. And if there is no matching key in the right data frame, it will insert null.

Here only data from employee data frame is selected and for department id "D1022" as there is no matching record in department's data frame, Spark has put null for that record.

Right Outer Join

Like left outer join, spark will only pick records from right data frame while doing right outer join. If there is no matching key in the left data frame, Spark will insert null.

Left Semi Joins

In case of left semi joins, Spark only picks data from left data frame for which it finds a common key in right data frame. Like most join types, this is better understood with an example.

We can notice that spark has data only from employee data frame and it has not picked employee id 4 as department id "D1022" is not present in department data frame.

Left Anti Join

Here, Spark picks data from left data frame only for those rows where it does not find a common key in the right data frame. For our example, it should only pick employee id 4 as it does not have matching dept_id in department's data frame.

Natural Join

In case of Natura joins, spark joins columns with same name and performs inner join and returns us result. Using implicate join condition is always dangerous. Like in our example, spark will join employee id to department id which is clearly wrong.

In most cases we should avoid using this join and specify our own joining condition.

Cross Join

In Cross join, Sparks joins each row from left data frame with each row in right data frame. This will result in huge data created in the cluster. We should we careful while using cross join as it can break our application.

Cross join have created so many rows for our small data frames. You can imagine what will happen if you do cross join on very large data. So be careful when using this join type.

Conclusion

In this blog, we have gone through spark join types and also written code for them. I hope you have found this useful. You can get all code in this blog at GitHub. See in the next article. Until then, keep learning.

Mahesh Mogal

I am passionate about Cloud, Data Analytics, Machine Learning, and Artificial Intelligence. I like to learn and try out new things. I have started blogging about my experience while learning these exciting technologies.

Stay Updated with Latest Blogs

Get latest blogs delivered to your mail directly.

Recent Posts

Spark Join Types With Examples

In this blog, we are going to learn different spark join types. We will also write code and validate data output for each join type to better understand them.

Read More
Integrate Spark with Jupyter Notebook and Visual Studio Code

In this blog, we are going to integrate spark with jupyter notebook and visual studio code to create easy-to-use development environment.

Read More
Reading Data From SQL Tables in Spark

In this blog, we are going to learn about reading data from SQL tables in Spark. We will create Spark data frames from tables and query results as well.

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram