3.4 Relational Algebra Operators

The eight relational algebra operators are

1. SELECT – To retrieve specific tuples/rows from a relation.

Ord# OrdDate Cust# 101 02-08-94 002 104 18-09-94 002

2. PROJECT – To retrieve specific attributes/columns from a relation.

Descr Price Power Supply 4000 101-Keyboard 2000 Mouse 800 MS-DOS 6.0 5000 MS-Word 6.0 8000

3. PRODUCT – To obtain all possible combination of tuples from two relations.

Ord# OrdDate O.Cust# C.Cust# CustName City 101 02-08-94 002 001 Shah Bombay 101 02-08-94 002 002 Srinivasan Madras 101 02-08-94 002 003 Gupta Delhi 101 02-08-94 002 004 Banerjee Calcutta 101 02-08-94 002 005 Apte Bombay 102 11-08-94 003 001 Shah Bombay 102 11-08-94 003 002 Srinivasan Madras

4. UNION – To retrieve tuples appearing in either or both the relations participating in the UNION.

Eg: Consider the relation Ord_Jul as follows (Table: Ord_Jul) Ord# OrdDate Cust# 101 03-07-94 001 102 27-07-94 003 101 02-08-94 002 102 11-08-94 003 103 21-08-94 003 104 28-08-94 002 105 30-08-94 005

Note: The union operation shown above logically implies retrieval of records of Orders placed in July or in August

5. INTERSECT- To retrieve tuples appearing in both the relations participating in the INTERSECT.

Eg: To retrieve Cust# of Customers who've placed orders in July and in August Cust# 003

6. DIFFERENCE – To retrieve tuples appearing in the first relation participating in the DIFFERENCE but not the second.

Eg: To retrieve Cust# of Customers who've placed orders in July but not in August Cust# 001

7. JOIN – To retrieve combinations of tuples in two relations based on a common field in both the relations.

Eg: ORD_AUG join CUSTOMERS (here, the common column is Cust#) Ord# OrdDate Cust# CustNames City 101 02-08-94 002 Srinivasan Madras 102 11-08-94 003 Gupta Delhi 103 21-08-94 003 Gupta Delhi 104 28-08-94 002 Srinivasan Madras 105 30-08-94 005 Apte Bombay

Note: The above join operation logically implies retrieval of details of all orders and the details of the corresponding customers who placed the orders.

Such a join operation where only those rows having corresponding rows in the both the relations are retrieved is called the natural join or inner join. This is the most common join operation.

Consider the example of EMPLOYEE and ACCOUNT relations.

EMPLOYEE

EMP #	EmpName	EmpCity	Acc#
X101	Shekhar	Bombay	120001
X102	Raj	Pune	120002
X103	Sharma	Nagpur	Null
X104	Vani	Bhopal	120003

ACCOUNT

Acc#	OpenDate	BalAmt
120001	30. Aug. 1998	5000
120002	29. Oct. 1998	1200
120003	1. Jan. 1999	3000
120004	4. Mar. 1999	500

A join can be formed between the two relations based on the common column Acc#. The result of the (inner) join is :

Emp#	EmpName	EmpCity	Acc#	OpenDate	BalAmt
X101	Shekhar	Bombay	120001	30. Aug. 1998	5000
X102	Raj	Pune	120002	29. Oct. 1998	1200
X104	Vani	Bhopal	120003	1. Jan 1999	3000

Note that, from each table, only those records which have corresponding records in the other table appear in the result set. This means that result of the inner join shows the details of those employees who hold an account along with the account details.

The other type of join is the outer join which has three variations – the left outer join, the right outer join and the full outer join. These three joins are explained as follows:

The left outer join retrieves all rows from the left-side (of the join operator) table. If there are corresponding or related rows in the right-side table, the correspondence will be shown. Otherwise, columns of the right-side table will take null values.

EMPLOYEE left outer join ACCOUNT gives:

Emp#	EmpName	EmpCity	Acc#	OpenDate	BalAmt
X101	Shekhar	Bombay	120001	30. Aug. 1998	5000
X102	Raj	Pune	120002	29. Oct. 1998	1200
X103	Sharma	Nagpur	NULL	NULL	NULL
X104	Vani	Bhopal	120003	1. Jan 1999	3000

The right outer join retrieves all rows from the right-side (of the join operator) table. If there are corresponding or related rows in the left-side table, the correspondence will be shown. Otherwise, columns of the left-side table will take null values.

EMPLOYEE right outer join ACCOUNT gives:

Emp#	EmpName	EmpCity	Acc#	OpenDate	BalAmt
X101	Shekhar	Bombay	120001	30. Aug. 1998	5000
X102	Raj	Pune	120002	29. Oct. 1998	1200
X104	Vani	Bhopal	120003	1. Jan 1999	3000
NULL	NULL	NULL	120004	4. Mar. 1999	500

(Assume that Acc# 120004 belongs to someone who is not an employee and hence the details of the Account holder are not available here)

The full outer join retrieves all rows from both the tables. If there is a correspondence or relation between rows from the tables of either side, the correspondence will be shown. Otherwise, related columns will take null values.

EMPLOYEE full outer join ACCOUNT gives:

Emp#	EmpName	EmpCity	Acc#	OpenDate	BalAmt
X101	Shekhar	Bombay	120001	30. Aug. 1998	5000
X102	Raj	Pune	120002	29. Oct. 1998	1200
X103	Sharma	Nagpur	NULL	NULL	NULL
X104	Vani	Bhopal	120003	1. Jan 1999	3000
NULL	NULL	NULL	120004	4. Mar. 1999	500

Q: What will the result of a natural join operation between R1 and R2 ?
A:

a1	b1	c1
a2	b2	c2
a3	b3	c3

8. DIVIDE

Consider the following three relations:

R1 divide by R2 per R3 gives:

a

Thus the result contains those values from R1 whose corresponding R2 values in R3 include all R2 values.