What is compound join?

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 Information and Emails:

Let’s understand how it works with SQL​ data. Suppose we have three tables, all of which contain one similar field.

Table Information

Table Emails

Table Earnings

See the problem? We need to filter out all the data containing Name, Email, Country, and 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.