In relational algebra, a *join* is the multiplication of two data sets followed by a restriction of the result so that only the intersection of the two data sets is returned. The whole purpose of the join is to horizontally merge two data sets and produce a new result set from the combination by matching rows in one data source to rows in the other data source, as shown in Figure 9.1. This section explains the various types of joins and how to use them to select data.

By merging the data using the join, the rest of the SQL SELECT statement, including the column expressions, aggregate groupings, and WHERE clause conditions, can access any of the columns or rows from the joined tables. These capabilities are the core and power of SQL.

Joins are based on the idea of intersecting data sets. As shown in Figure 9.2, a relational join deals with two sets of data that have common values, and it's these common values that define how the tables intersect.

The intersection simply represents that some common column can connect a row ...

Start Free Trial

No credit card required