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.
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 (also known as subqueries) 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 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');
Note: For details about the operators used in conjunction with subselects, see the chapter “Understanding the Elements of SQL Statements.”
ANSI/ISO Join Syntax
In addition to performing joins using the approach described in the Joins section, ANSI syntax described in the 1992 ANSI/ISO SQL standard can be used. The ANSI syntax provides a more precise way of specifying joins that are otherwise identical to those produced from the traditional syntax. The ANSI syntax also allows the specification of outer joins.
An outer join returns not only the rows of the join sources that join together according to a specified search condition, but also rows from one or both sources that do not have a matching row in the other source. For rows included in the outer join that do not have a matching row from the other source, null values are returned in all columns of the other source.
An outer join is the union of two SELECT statements: the first query returns rows that fulfill the join condition and the second query returns nulls for rows that do not fulfill the join condition.
The ANSI syntax is specified in the FROM clause, as follows:
source join_type JOIN source ON search_condition
or
source join_type JOIN source USING (column {,column})
or
source CROSS JOIN source
where:
source
Specifies the table, view, or join where the data for the left or right side of the join originates.
join_type
Specifies the type of join as one of the following:
INNER
(Default) Specifies an inner join.
LEFT [OUTER]
Specifies a left outer join, which returns all values from the left source.
RIGHT [OUTER]
Specifies a right outer join, which returns all values from the right source.
FULL [OUTER]
Specifies a full outer join, which returns all values from both left and right sources.
Note: RIGHT and LEFT joins are the mirror image of each other: (table1 RIGHT JOIN table2) returns the same results as (table2 LEFT JOIN table1).
ON search_condition
Is a valid restriction, subject to the rules for the WHERE clause. The search_condition must not include aggregate functions or subselects. Matching pairs of rows in the join result are those that satisfy the search_condition.
USING (column {,column})
Is an alternate form of the search_condition. Each column in the USING clause must exist unambiguously in each join source. An ON search_condition is effectively generated in which the search_condition compares the columns of the USING clause from each join source.
CROSS JOIN
Is a cross product join of all rows of the join sources.
By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses. A join source can itself be a join, and the results of joins can be joined with the results of other joins, as illustrated in the following pseudocode:
(A join B) join (C join D)
The placement of restrictions is important in obtaining correct results. For example:
A join B on cond1 and cond2
does not return the same results as:
A join B on cond1 where cond2
In the first example, the restriction determines which rows in the join result table are assigned null values; in the second example, the restriction determines which rows are omitted from the result table.
The following examples are identical and use an outer join in the FROM clause to display all employees along with the name of their department, if any. One uses the ON clause and the other uses an equivalent USING clause:
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
ON e.dept = d.dept);
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
USING (dept));
Cross Join Example
The following is an example of a cross join. A cross join returns a Cartesian product. Every row of one table is combined with every row of another table.
SELECT
e.last_name,
d.dept_name
FROM
emp e CROSS JOIN dept d;
last_name dept_name
Smith HR
Smith Sales
Smith Admin
Smith Support
Smith Services
Jones HR
Jones Sales
Jones Admin
Jones Support
Jones Services
Green HR
Green Sales
Green Admin
Green Support
Green Services
White HR
White Sales
White Admin
White Support
White Services
Mustard HR
Mustard Sales
Mustard Admin
Mustard Support
Mustard Services
Scarlet HR
Scarlet Sales
Scarlet Admin
Scarlet Support
Scarlet Services
The cross join is equivalent to joining tables and leaving off the WHERE clause.
SELECT
e.last_name,
d.dept_name
FROM
emp e,
dept d;