Intro to the join operation

Combining information from multiple tables

There is only one operation in Relational Algebra than can combine the information from multiple tables:

Example of a cartesian product operation:

(Ignore the select command in the example)

Employee: select fname, lname, salary, dno from employee; +--------+---------+----------+-----+ | fname | lname | salary | dno | +--------+---------+----------+-----+ | John | Smith | 30000.00 | 5 | | Frankl | Wong | 40000.00 | 5 | | Alicia | Zelaya | 25000.00 | 4 | | Jennif | Wallace | 43000.00 | 4 | | Ramesh | Narayan | 38000.00 | 5 | | Joyce | English | 25000.00 | 5 | | Ahmad | Jabbar | 25000.00 | 4 | | James | Borg | 55000.00 | 1 | +--------+---------+----------+-----+
Department: select dname, dnumber from department; +----------------+---------+ | dname | dnumber | +----------------+---------+ | Research | 5 | | Administration | 4 | | Headquarters | 1 | +----------------+---------+
Exmployee × Department: select fname, lname, salary, dno, dname, dnumber from employee, department; +--------+---------+----------+-----+----------------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | John | Smith | 30000.00 | 5 | Administration | 4 | | John | Smith | 30000.00 | 5 | Headquarters | 1 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Administration | 4 | | Frankl | Wong | 40000.00 | 5 | Headquarters | 1 | | Alicia | Zelaya | 25000.00 | 4 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Administration | 4 | | Alicia | Zelaya | 25000.00 | 4 | Headquarters | 1 | | Jennif | Wallace | 43000.00 | 4 | Research | 5 | | Jennif | Wallace | 43000.00 | 4 | Administration | 4 | | Jennif | Wallace | 43000.00 | 4 | Headquarters | 1 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Administration | 4 | | Ramesh | Narayan | 38000.00 | 5 | Headquarters | 1 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Administration | 4 | | Joyce | English | 25000.00 | 5 | Headquarters | 1 | | Ahmad | Jabbar | 25000.00 | 4 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Administration | 4 | | Ahmad | Jabbar | 25000.00 | 4 | Headquarters | 1 | | James | Borg | 55000.00 | 1 | Research | 5 | | James | Borg | 55000.00 | 1 | Administration | 4 | | James | Borg | 55000.00 | 1 | Headquarters | 1 | +--------+---------+----------+-----+----------------+---------+

Notice that:

Every tuple in relation Employee is combined with every tuple in relation Department

Some tuple combinations are useful

Example:

Employee Department +--------+---------+----------+-----+ +----------------+---------+ | John | Smith | 30000.00 | 5 | with | Research | 5 | +--------+---------+----------+-----+ +----------------+---------+ resulting in: +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | +--------+---------+----------+-----+----------------+---------+

The combined tuple is useful because:

The combined tuple now contains additional information (e.g.: the department name) for the employee

Some tuple combinations as useless.

Example:

Employee Department +--------+---------+----------+-----+ +----------------+---------+ | John | Smith | 30000.00 | 5 | with | Administration | 4 | +--------+---------+----------+-----+ +----------------+---------+ resulting in: +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Administration | 4 | +--------+---------+----------+-----+----------------+---------+

The combined tuple is useless because:

The additional information (e.g.: the department name "Adminstration") does not belong to the employee !!!

Extracting (post-processing) the useful combinations of a cartesian product

Important fact:
Therefore:

Consider the result of Employee × Department:

Employee × Department: +--------+---------+----------+-----+----------------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | John | Smith | 30000.00 | 5 | Administration | 4 | | John | Smith | 30000.00 | 5 | Headquarters | 1 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Administration | 4 | | Frankl | Wong | 40000.00 | 5 | Headquarters | 1 | | Alicia | Zelaya | 25000.00 | 4 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Administration | 4 | | Alicia | Zelaya | 25000.00 | 4 | Headquarters | 1 | | Jennif | Wallace | 43000.00 | 4 | Research | 5 | | Jennif | Wallace | 43000.00 | 4 | Administration | 4 | | Jennif | Wallace | 43000.00 | 4 | Headquarters | 1 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Ramesh | Narayan | 38000.00 | 5 | Administration | 4 | | Ramesh | Narayan | 38000.00 | 5 | Headquarters | 1 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Administration | 4 | | Joyce | English | 25000.00 | 5 | Headquarters | 1 | | Ahmad | Jabbar | 25000.00 | 4 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Administration | 4 | | Ahmad | Jabbar | 25000.00 | 4 | Headquarters | 1 | | James | Borg | 55000.00 | 1 | Research | 5 | | James | Borg | 55000.00 | 1 | Administration | 4 | | James | Borg | 55000.00 | 1 | Headquarters | 1 | +--------+---------+----------+-----+----------------+---------+

Question:

What relational operation that we have learned so far can you use to extract the useful subset of the cartesian product Exmployee × Department ???

Answer:

σ _{dno = dnumber} ( Employee × Department )

The tuples in Employee × Department that satisfy the condition dno = dnumber are:

σ _{dno = dnumber} ( Employee × Department ): +--------+---------+----------+-----+----------------+---------+ | fname | lname | salary | dno | dname | dnumber | +--------+---------+----------+-----+----------------+---------+ | John | Smith | 30000.00 | 5 | Research | 5 | | Frankl | Wong | 40000.00 | 5 | Research | 5 | | Alicia | Zelaya | 25000.00 | 4 | Administration | 4 | | Jennif | Wallace | 43000.00 | 4 | Administration | 4 | | Ramesh | Narayan | 38000.00 | 5 | Research | 5 | | Joyce | English | 25000.00 | 5 | Research | 5 | | Ahmad | Jabbar | 25000.00 | 4 | Administration | 4 | | James | Borg | 55000.00 | 1 | Headquarters | 1 | +--------+---------+----------+-----+----------------+---------+

The (inner) join operation ⋈

Fact:

For convenience, we define the (join) operation ⋈ as follows:

R₁ ⋈_Cond R₂ = σ_Cond ( R₁ × R₂ )

Example: