Join

It happens often that data from multiple realtions are required in a query. For this, relations need to be linked. The identical attributes (foreign keys) in both relations are linked.

There are two ways to link relations in queries:

in the WHERE part

In this option, the identical attributes are linked with the comparison operator and inserted as "condition" (not a real condition) into the WHERE part of the query.

Basically, e.g. a < can also be used for a join. However, this is usually used in combination with a join condition with = since such a join alone does not make sense.

in the FROM part

Another possibility is to do the join in the FROM part of a query. This makes more sense because the actual search criteria (conditions in the WHERE part) are separated from the table joins.

The following commands are available for this purpose:

• <Relation> JOIN <Relation> USING (<Attribute>)
  the relations are joined by an attribute with the same name in both tables
• <Relation> NATURAL JOIN <Relation>
  automatically joins all attributes that are of the same name in both relations.
• <Relation> JOIN <Relation> ON <Attribute> <comparison operator> <Attribute>
  with this command, you can decide by which attributes the relations should be joined and which operator is to be used (there can be more than one join as well).

Using these commands, the example from above would look like the following:

SELECT name, surname, newspaper_name

FROM customer JOIN subscription USING (CustNo);

or

SELECT name, surname, newspaper_name

FROM customer JOIN subscription ON customer.CustNo = subscription.CustNo;

or

SELECT name, surname, newspaper_name

FROM customer NATURAL JOIN subscription;

The commands above only return datasets present in both relations. Should all datasets of a relation be returned with the corresponding datasets of the second relation, the following commands are applied:

• <Table> RIGHT OUTER JOIN <Table> USING (<Attribute>)
  all datasets of the right relation and the corresponding datasets of the left relation
• <Table> LEFT OUTER JOIN <Table> USING (<Attribute>)
  all datasets of the left relation and the corresponding datasets of the right relation

If there are no joins to be made in the left relation using RIGHT OUTER JOIN, NULL is returned for the attributes of this relation. The same is true for the opposite (using LEFT OUTER JOIN).