Joining Relations in SQL

Comparisons in the where clause are used to combine rows from the tables listed in the from clause.
Example: In the table EMP only the numbers of the departments are stored, not their
name. For each salesman, we now want to retrieve the name as well as the
number and the name of the department where he is working:
select ENAME, E.DEPTNO, DNAME
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO
and JOB = ’SALESMAN’;
Explanation: E and D are table aliases for EMP and DEPT, respectively. The computation of the
query result occurs in the following manner (without optimization):
1. Each row from the table EMP is combined with each row from the table DEPT (this operation
is called Cartesian product). If EMP contains m rows and DEPT contains n rows, we
thus get n  m rows.
2. From these rows those that have the same department number are selected (where
E.DEPTNO = D.DEPTNO).
3. From this result finally all rows are selected for which the condition JOB = ’SALESMAN’
holds.
In this example the joining condition for the two tables is based on the equality operator “=”.
The columns compared by this operator are called join columns and the join operation is called an equijoin.
Any number of tables can be combined in a select statement.
Example: For each project, retrieve its name, the name of its manager, and the name of
the department where the manager is working:
select ENAME, DNAME, PNAME
from EMP E, DEPT D, PROJECT P
where E.EMPNO = P.MGR
and D.DEPTNO = E.DEPTNO;

It is even possible to join a table with itself:
Example: List the names of all employees together with the name of their manager:
select E1.ENAME, E2.ENAME
from EMP E1, EMP E2
where E1.MGR = E2.EMPNO;
Explanation: The join columns are MGR for the table E1 and EMPNO for the table E2.
The equijoin comparison is E1.MGR = E2.EMPNO.