### 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:

 The cartesian product (×) operation

• 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:

 The result of Employee × Department is also a relation !!!

Therefore:

 We can apply any relational algebra operation on the result of Employee × Department !!!!

• 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 ???

 ``` σ 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:

 The cartesian product (×) operation will often be followed by a selection operation (σ) to produce a meaningful combination of tuples from multiple relations

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

 ``` R1 ⋈Cond R2 = σCond ( R1 × R2 ) ```

• 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 | +----------------+---------+ Employee ⋈dno=dnumber Department = σ 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 | +--------+---------+----------+-----+----------------+---------+ ```