Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
join
compound join

What is compound join?

Educative Answers Team

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:

svg viewer

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
33 muhammad43@live.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
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring