The 'Join' Family Operators
In our select statements, we seperate tables in the
FROMclause by commas, and that is implicitly a cross product of those tables.
SELECT A1, A2, . . . , A(n)
FROM R1, R2, . . . , R(m)
However, there is a way to explicitly join two or more tables using one of the
JOINoperators. There are a few different types, and they are listed below:
INNER JOINon condition
- This kind of join operator takes the cross product of the tables and then applies a condition, and only taking the cross product elements that satisfy the condition given. It then eliminates all duplicate columns that are created.
NATURAL JOINoperator equates all columns with the same name in the tables that are being joined. It requires the values in the columns to be the same in order to keep the elements in the cross product. This type of join also eliminates any duplicate columns that are created.
INNER JOIN USING(attrs)
- This again is an
INNER JOIN, however this type of join takes a special clause called
USINGand listing attributes. This is sort of like the
NATURAL JOIN, except you specifically state the attributes that you want to be equated.
- There are multiple forms of this kind of join operator. There is the
LEFT OUTER JOIN, the
RIGHT OUTER JOIN, and the
FULL OUTER JOIN. These joins combine elements similar to the
INNER JOIN, except when elements don't match the
INNER JOINcondition, they're still added to the result and padded with
All of these join operators don't add any specific power to SQL, they can all be described using different constructs, however they can be very helpful when creating queries. Especially the
OUTER JOIN, for it is very difficult to express without the