The preceding sections discussed the most important join forms. This section shows you three other forms:
- Theta join
- Self-join
- Semi-join
The following subsections describe these forms.
Theta Join
Join columns need not be compared using the equality sign. A join operation using a general join condition is called a theta join. Example 6.67, which uses the employee_enh table, shows the theta join operation.
Get all the combinations of employee information and department information where the domicile of an employee alphabetically precedes any location of departments.
USE sample;
SELECT emp_fname, emp_lname, domicile, location
FROM employee_enh JOIN department
ON domicile < location;
Code language: PHP (php)
The result is
In Example 6.67, the corresponding values of columns domicile and location are compared. In every resulting row, the value of the domicile column is ordered alphabetically before the corresponding value of the location column.
Self-Join, or Joining a Table with Itself
In addition to joining two or more different tables, a natural join operation can also be applied to a single table. In this case, the table is joined with itself, whereby a single column of the table is compared with itself. The comparison of a column with itself means that the table name appears twice in the FROM clause of a SELECT statement.
Therefore, you need to be able to reference the name of the same table twice. This can be accomplished using at least one alias name. The same is true for the column names in the join condition of a SELECT statement. In order to distinguish both column names, you use the qualified names. Example 6.68 joins the department table with itself.
Get full details of all departments located at the same location as at least one other department:
USE sample;
SELECT t1.dept_no, t1.dept_name, t1.location
FROM department t1 JOIN department t2
ON t1.location = t2.location
WHERE t1.dept_no <> t2.dept_no;
Code language: PHP (php)
The result is
The FROM clause in Example 6.68 contains two aliases for the department table: t1 and t2. The first condition in the WHERE clause specifies the join columns, while the second condition eliminates unnecessary duplicates by making certain that each department is compared with different departments.
Semi-Join
The semi-join is similar to the natural join, but the result of the semi-join is only the set of all rows from one table where one or more matches are found in the second table.
Example 6.69 shows the semi-join operation.
USE sample;
SELECT emp_no, emp_lname, e.dept_no
FROM employee e JOIN department d
ON e.dept_no = d.dept_no
WHERE location = 'Dallas';
Code language: PHP (php)
The result is
As can be seen from Example 6.69, the SELECT list of the semi-join contains only columns from the employee table. This is exactly what characterizes the semi-join operation. This operation is usually used in distributed query processing to minimize data transfer. The Database Engine uses the semi-join operation to implement the feature called star join.