Database Concepts - Data Warehouse Systems: Design and Implementation - page 32

For example, the query “List all product names and category names”
reads:

Temp ← Products ∗ Categories
Result ← π ProductName,CategoryName ( Temp ) .

The first query performs the natural join between relations Products and
Categories . The attributes in Temp are all the attributes in Product , plus all
the attributes in Categories , except for CategoryID , which is in both relations,
so only one of them is kept. The second query performs the final projection.
The joins introduced above are known as inner joins , since tuples that do
no match the join condition are eliminated. In many practical cases, we need
to keep in the result all the tuples of one or both relations, independently
of whether or not they satisfy the join condition. For these cases, a set of
operations, called outer joins , were defined. There are three kinds of outer
joins: left outer join, right outer join, and full outer join.
The left outer join , denoted R S , performs the join as defined above,
but instead of keeping only the matching tuples, it keeps every tuple in R
(the relation of the left of the operation). If a tuple in R does not satisfy
the join condition, the tuple is kept, and the attributes of S in the result are
filled with null values.
As an example, the query “Last name of employees, together with the last
name of their supervisor, or null if the employee has no supervisor,” reads in
relational algebra:

Supervisors ← ρ EmployeeID → SupID , LastName → SupLastName ( Employees )
Result ← π EmployeeID,L a stName,SupID,SupLastName (

Employees ReportsTo=SupID Supervisors )

The result is given in the following table.

EmployeeID LastName SupID SupLastName
1

Davolio 2 Fuller

2 Fuller NULL NULL

3 Leverling 2 Fuller

... ... ... ...

We can see that employee 2 does not report to anybody; therefore, his
supervisor data contain null values.
The right outer join , denoted R S , is analogous to the left outer join,
except that the tuples that are kept are the ones in S .The full outer join ,
denoted R S , keeps all the tuples in both R and S .
Suppose that in the previous example, we also require the information of
the employees who do not supervise anyone. Then, we would have:

π EmployeeID,LastName,SupID,SupLastName ( Employees ReportsTo=SupID Supervisors )