4.3. Equi-Joins and Non-Equi-Joins

Joins with comparison (non-equal) operatorsthat is, =, >, >=, <, <=, and <>--on the WHERE or ON clauses are called theta joins, where theta represents the relational operator. Inner joins with an = operator are called equi-joins and joins with an operator other than an = sign are called non-equi-joins .

4.3.1. Equi-Joins

The most common join involves join conditions with equality comparisons. Such a join, where the comparison operator is = in the WHERE or ON clause, is called an equi-join. The following is an example:

SELECT *
FROM Course c JOIN Prereq p
ON c.course_number=p.course_number

Another way to look at a join of any kind is that it is the Cartesian product with an added condition. The output for this query has been shown earlier in this chapter. You will note that the result of the join is simply the Cartesian product with the rows where the course numbers are equal. Per the output, you will see that this query displays all rows that have course_number in the Course table equal to course_number in the Prereq table. All the join columns have been included in this result set. This means that course_number has been shown twiceonce from the Course table, and once from the Prereq tableand, this duplicate column is of course redundant.

4.3.2. Non-Equi-Joins

Joins that do not test for equality are non-equi-joins. Non-equi-joins are rare. The following section on self joins provides an example of a theta join without an equality (=) operator (a non-equi join).