Relational Algebra Operations Continued

Outer Join

The outer join is an extension of the join operation to deal with missing information. Suppose that we have the relations with the following schemas, which contain data on full-time employees.

employee(employee_name, street, city)

ft_works(employee_name, branch_name, salary)

Table 1 : Table employee

employee_name	street	city
Kamal	Elephant Rd	Dhaka
Jamal	Mirpur Rd	Dhaka
Shumon	New Market Rd	Chittagong
Hena	Dorga Gate Rd	Sylhet

Table 1 (continued) : Table ft_works

employee_name	branch_name	Salary
Kamal	Kakrail	10000
Jamal	Motijheel	15000
Swarna	Rampura	20000
Hena	Dhanmondi	25000

Consider the employee and ft_works relations in Table 1. Suppose that we want to generate a single relation with all the information (street, city, branch_name and salary) about full_time employees. A possible approach would be to use the natural join operation as follows:

employee  ∞ ft_works

The result of this expression appears in Table 2.

Table 2: employee ∞ ft_works

employee_name	street	city	branch_name	salary
Kamal	Elephant Rd	Dhaka	Kakrail	10000
Jamal	Mirpur Rd	Dhaka	Motijheel	15000
Hena	Dorga Gate Rd	Sylhet	Dhanmondi	25000

Note that we have lost the street and city information about Shumon, since the tuple describing Shumon is absent from the ft_works relation; similarly, we have lost the branch_name and salary information about Swarna, since the tuple describing Swarna is absent from the employee relation.

We can use the outer join operation to avoid this loss of information. There are actually three forms of the operation: left outer join, denoted by, 

right  outer join, denoted by, 

and full outer join, denoted by,

All three forms of outer join compute the join, and add extra tuples to the result of the join. The results of the expressions employee left outer join ft_works, employee right outer join ft_works and

employee full outer join ft_works appear in Tables 3, 4 and 5 respectively.

The left outer join takes all tuples in the left relation that did not match with any tuple in the right relation, pads the tuples with null values for all other attributes from the right relation, and adds them to the result of the natural join. In Table 3 tuple (Shumon, New Market Rd, Chittagong, Null, Null) is such a tuple. All information from the left relation is present in the result of the left outer join.

The right outer join is symmetric with the left outer join. It pads tuples from the right relation that did not match any from the left relation with nulls and adds them to the result of the natural join. In Table 4, tuple (Swarna, Null, Null, Rampura, 20000) is such a tuple.

The full outer join does both of these operations, padding tuples from the left relation that did not match any from the right relation, as well as tuples from the right relation that did not match any from the left relation, and adding them to the result of the join. Table 5 shows the result of a full outer join.

   

Table 3:  employee left outer join ft_works

employee_name	street	City	branch_name	salary
Kamal	Elephant Rd	Dhaka	Kakrail	10000
Jamal	Mirpur Rd	Dhaka	Motijheel	15000
Shumon	New Market Rd	Chittagong	Null	Null
Hena	Dorga Gate Rd	Sylhet	Dhanmondi	25000

Table 4: employee  right outer join ft_works

employee_name	street	City	branch_name	salary
Kamal	Elephant Rd	Dhaka	Kakrail	10000
Jamal	Mirpur Rd	Dhaka	Motijheel	15000
Swarna	Null	Null	Rampura	20000
Hena	Dorga Gate Rd	Sylhet	Dhanmondi	25000

Table 5: employee full outer join ft_works

employee_name	street	City	branch_name	salary
Kamal	Elephant Rd	Dhaka	Kakrail	10000
Jamal	Mirpur Rd	Dhaka	Motijheel	15000
Shumon	New Market Rd	Chittagong	Null	Null
Swarna	Null	Null	Rampura	20000
Hena	Dorga Gate Rd	Sylhet	Dhanmondi	25000