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:

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.eno = m.eno

Tables can be joined on any number of related columns. The data types of the join columns must be comparable.

Join Relationships

The simple joins illustrated in the two preceding examples depend on equal values in the join columns. This type of join is called an equijoin. Other types of relationships can be specified in a join. For example, the following query lists salespersons who have met or exceeded their sales quota:

select s.name, s.sales_ytd
       from sales s, quotas q
       where s.empnum = d.empnum and
              s.sales_ytd >= d.quota;

Subselects

Subselects are select statements placed in a WHERE or HAVING clause; the results returned by the subselect are used to evaluate the conditions specified in the WHERE or HAVING clause. Subselects are also referred to as subqueries.

Subselects must return a single column, and cannot include an order by or union clause.

The following example uses a subselect to display all employees whose salary is above the average salary:

select * from employees where salary >
       (select avg(salary) from employees);

In the preceding example, the subselect returns a single value: the average salary. Subselects can also return sets of values. For example, the following query returns all employees in all departments managed by Barth.

select ename from employees where edept in
       (select ddept from departments
              where dmgr = 'Barth');

For details about the operators used in conjunction with subselects, see the chapter "Understanding the Elements of SQL Statements."