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
Department Table
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.