Table of Contents Link to heading

• JOIN keyword
  • Joined attributes
• Table Aliases
• INNER JOIN keyword
• Duplicate column names
• Multiple attributes in a join condition
• OUTER JOIN keyword
  • JOIN | INNER JOIN
• LEFT JOIN keyword
• RIGHT JOIN keyword
• FULL JOIN keyword
• Summary of JOIN

JOIN keyword Link to heading

Tables (relations) can be combined to form a new relation using the JOIN keyword.

This relation can have a combination of the attributes from the tables used in the query.

JOIN syntax:

SELECT <selectList> FROM TableA JOIN TableB
ON TableA.key = TableB.key;

For example:

TableA

TableB

Combine TableA and TableB produces TableC.

SELECT * FROM TableA JOIN TableB ON TableA.addID = TableB.addID

TableC (result)

Joined attributes Link to heading

To re-create the information captured in the data, relations must be merged together based on conditions that specify which attributes in one relation match the same data in the attributes of the other relation.

This ordinarily follows the rule: TableB(FK) ~> TableA(PK) attributes.

In the above database design:

TableA.addID = TableB.addID

The addID attribute of TableA and the addID attribute of TableB are called the joined attributes.

Table Aliases Link to heading

When querying, you may want to improve the readability of the JOIN queries by renaming your relations with an alias.

It is useful where the relation is used more than once to distinguish between different instances of the relation.

For example, given the following relational schemas:

Department(deptName, address, city)
PK(deptName)

Employee(firstName, lastName, dept, office, salary, city)
PK(firstName, lastName)
FK(dept) -> Department(deptName)

Bad design:

SELECT Employee.firstName, Department.city
FROM Department JOIN Employee
ON Department.deptName = Employee.dept
WHERE lastName = 'Brown';

Better design:

SELECT E.firstName, D.city
FROM Department AS D JOIN Employee AS E
ON D.deptName = E.dept
WHERE lastName = 'Brown';

INNER JOIN keyword Link to heading

The type of joining two relations we have been discussing so far is the same as using the INNER JOIN keyword.

• Only returns connected, matching tuples from both relations.

For an explanation of why it is called an INNER JOIN, refer to the following algorithm:

for each employee x in Employee
    for each department y in Department
        if x.dept == y.deptname then
            output x + y tuple
        end if
    end for y
end for x

If an employee’s tuple does not match any department’s tuple in a join, the employee’s tuple is said to be dangling and thus dropped from the output.

The following sql statements have the same semantic and output:

SELECT * FROM Department, Employee WHERE Employee.dept = Department.deptName
SELECT * FROM Department JOIN Employee ON Employee.dept = Department.deptName
SELECT * FROM Department INNER JOIN Employee ON Employee.dept = Department.deptName

Duplicate column names Link to heading

Using the same relational database as above, for each employee with lastName Brown, list their firstName, the city where they live and the city in which they work.

• Use the table’s name to distinguish duplicate column names.

Incorrect query (ambiguous column name):

SELECT firstName, city, city
FROM Department AS D JOIN Employee AS E ON deptName = dept
WHERE lastName = 'Brown';

Correct query:

SELECT firstName, E.city, D.city
FROM Department AS D JOIN Employee AS E ON deptName = dept
WHERE lastName = 'Brown';

Note that “firstName” is unambiguous hence it does not need to be qualified with the table from which it comes from.

Multiple attributes in a join condition Link to heading

OUTER JOIN keyword Link to heading

This keyword is a variant of the JOIN keyword that keeps the dangling tuples in the result.

It is achieved by padding missing values with NULL where the tuples is dangling (do not match in both relations).

JOIN | INNER JOIN Link to heading

There are three variants of the OUTER JOIN keyword:

LEFT JOIN keyword Link to heading

Only dangling tuples of the left of the JOIN keyword are padded with NULL values.

• LEFT JOIN | LEFT OUTER JOIN
• Returns all connected and unconnected tuples from the left relation (NULLs in right).

RIGHT JOIN keyword Link to heading

Only dangling tuples of the right of the JOIN keyword are padded with NULL values.

• RIGHT JOIN | RIGHT OUTER JOIN
• Returns all connected and unconnected tuples from the right relation (NULLs in left).

FULL JOIN keyword Link to heading

Dangling tuples of both sides of the JOIN keyword are padded with NULL values.

• FULL JOIN | FULL OUTER JOIN
• Returns all connected and unconnected tuples from the both left and right relation.

Summary of JOIN Link to heading

All of the JOIN keyword variants can be summarised as:

Credit: C.L. Moffatt (2008)