Related Tags

sql
join
compound join

# 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

CustID Name Country
22 Michael USA
14 Ali Pakistan
31 James UK

## Table Emails

CustID Email
22 michael123@gmail.com
31 james.arthur@yahoo.com

## Table Earnings

CustID Earning (dollars)
22 4350
90 6940
31 1232

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:

Name Email Country Earning
Michael USA michael123@gmail.com 4350
James UK james.arthur@yahoo.com 1232

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.

RELATED TAGS

sql
join
compound join