A compound join joins two tables and are significant when columns of multiple tables have a unique key to join to another table, as in the case of a relational database.
A compound join can be used in combination with a self join, inner join, or outer join. In this shot, we will look at an example of an inner join.
The concept of intersection of sets, ff you recall from math, lists down the “common” or “similar” values in two sets. This is the functionality of inner join except it finds the “common values” among two tables. This is showed in the illustration below, where the blue region represents the intersection of the two tables
Let’s understand how it works with SQL data. Suppose we have three tables, all of which contain one similar field.
See the problem? We need to filter out all the data containing
Earning for all the values in all three tables with the same
CustID and an
Earning greater than $1200. We will need a combination of two inner join functions to evaluate the final result:
SELECT Name, Email, Country, Earning FROM Information AS info INNER JOIN ON info.CustID= Emails.CustID INNER JOIN ON Emails.CustID= Earnings.CustID AND Earnings.Earning >=1200
The resulting table looks something like this:
We wrote an SQL query to perform on the above dataset through the inner join. Since we performed the inner join twice, it is an application of compound join in SQL.
View all Courses