Was this helpful?
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.
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;
Last modified date: 11/28/2023