#sql join

PySpark Join Two or Multiple DataFrames

PySpark DataFrame has a join() operation which is used to combine columns from two or multiple DataFrames (by chaining join()), in this article, you will learn how to do a PySpark Join on Two or Multiple DataFrames by applying conditions on the same or different columns. also, you will learn how to eliminate the duplicate columns on the result DataFrame and joining on multiple columns. Note:…

Spark SQL Inner Join Explained

Similar to SQL, Spark also supports Inner join to join two DataFrame tables, In this article, you will learn how to use an Inner Join on DataFrame with Scala example. Also, you will learn different ways to provide Join condition.

Inner join is the default join in Spark and it’s mostly used, this joins two datasets on key columns and where keys don’t match the rows get dropped from both…

Spark SQL Self Join Explained

Similar to SQL, Spark SQL also provides to Self join to join a DataFrame or table to itself, In this article, you will learn how to use a Self Join on multiple DataFrame tables with Scala example. Also, you will learn different ways to provide Join condition.

Before we jump into how to use self join expression, first, let’s create a DataFrame from  emp dataset, On this dataset we have an…

Join in database in fact carries the same meaning as in English dictionary. It connects two tables in a database through a common column or key. If we want to find the customers who live in California and have bought Product A, we will need to “Join” or connect the three tables(mentioned in my previous article) through common keys.The common key that connects Customers and Orders table is CustomerID and the common key that connects the Products table and Order table is ProductID.

In this particular scenario, since we are looking for customers who live in ‘CA’ and bought Product ‘A’, (i.e. intersection of two data sets), we will be using “Inner Join”. Let’s write the query, one step at a time. First, let’s join the Customer table with Orders table.

Select a.CustomerID,a.State,b.ProductID From Customers a Join Orders b On a.CustomerID=b.CustomerID Where a.state='CA'

The query above will return the following rows.

Here, ProductID does not really give any information about the Product. However, ProductID is the common key between Orders Table and Products Table. And Product Table hold information about the product with ProductID 1. Let's join the Products table to the above query.

Select a.CustomerID,a.State,b.ProductID,p.Product From Customers a Join Orders b On a.CustomerID=b.CustomerID Join Products p On b.ProductID = p.ProductID Where a.state='CA' And Product='A'

This above query will return the same number of records but notice the Product column (highlighted).

Try this exercise of using inner join with various small data sets. Write the SQL and then manually check against Venn diagram representing the data sets.

Now let’s look at the second scenario where you want to find out what all customers living in California ordered. Select a.CustomerID,a.State,b.ProductID,p.Product,b.Quantity From Customers a left Join Orders b On a.CustomerID=b.CustomerID left Join Products p On b.ProductID = p.ProductID Where state='CA'

Notice that you now see all 5 California customers in the list as well as the additional information of products each one have bought so far. Left join is primarily used to get additional optional information or to distinguish between customers who have certain data from those who do not. In this scenario we learned that 2 of 5 California customers have bought product A and the other three customers have not.

You may initially find it challenging to decide on when to use left join. In that scenario, visualize the tables or data set and Venn diagram. If we go back to Venn diagram, you see all the 5 CustomerID in the California set and CustomerID { 3,12} from the Product A set.

Now, let’s look at the third scenario, where you want to see all the customers who live in California or have bought Product A.  “or” is represented as union in Set Theory which is equivalent to full outer join in database. As per the above diagram, Union of California and Product A will return all the CustomerID inside the two circles. {3,4,5,6,7,8,12}. The SQL query that represents the above scenario will look like this.

Select a.CustomerID,a.State,b.ProductID,p.Product,b.Quantity From Customers a full outer Join Orders b On a.CustomerID=b.CustomerID full outer Join Products p On b.ProductID = p.ProductID Where state='CA' or Product='A'

Resulting rows contains all California customers as well as customers who bought product ‘A’