4. SQL Statements : SELECT : Joins
 
Share this page                  
Joins
Joins combine information from multiple tables and views into a single result table, according to column relationships specified in the WHERE clause.
For example, given the following two tables:
Employee Table
ename
edeptno
Benny Barth
10
Dean Reilly
11
Rudy Salvini
99
Tom Hart
123
Department Table
ddeptno
dname
10
Lumber
11
Sales
99
Accounting
123
Finance
The following query joins the two tables on the relationship of equality between values in the edeptno and ddeptno columns. The result is a list of employees and the names of the departments in which they work:
SELECT ename, dname FROM employees, departments
       WHERE edeptno = ddeptno;
A table can be joined to itself using correlation names; this is useful when listing hierarchical information. For example, the following query displays the name of each employee and the name of the manager for each employee.
SELECT e.ename, m.ename
       FROM employees e, employees m
       WHERE e.mgr = m.eno
Tables can be joined on any number of related columns. The data types of the join columns must be comparable.