Database Reference
In-Depth Information
10.3.5
Equi-Joins, Anti-Joins, and Range Joins
Equi-, anti-, and range joins are not join types in themselves but more oper-
ators applied within joins. A brief theoretical explanation is warranted
because of potential effects on performance.
1
Equi-Join
. This join simply uses an equals sign = between two col-
umns in a join. An equi-join is the fastest join type because it can find
an exact match (a single row). An equi-join is best used on unique
indexes such as primary keys.
Anti-Join
. This type of join uses the “not equal to” symbols: <> or !=.
An anti-join can also use “NOT (a=b)” syntax to reverse an equi-join.
Anti-joins should be avoided if possible because they will read all rows
in a table. If you are trying to read a single row from one million rows,
a lot of time will be wasted finding a row not matching a condition.
Range Join
. In this case, a range scan is required using the <, >, or
BETWEEN operators.
The [NOT] IN clause
. The IN clause allows value checking against a
list of items and is sometimes known as a semi-join. A semi-join is
not really a join but more like a half-join. The IN list can be a list of
literal values or a subquery. Beware of a subquery returning a large
number of rows (see Chapter 12). The optional NOT clause implies
an anti-join. The IN clause is best used with a fixed number of pre-
defined literal values.
The [NOT] EXISTS clause
. See Chapter 12. EXISTS is similar to
IN except it can be more efficient. Again, because the NOT modifier
reverses the logic and creates an anti-join, avoid using NOT EXISTS
if possible.
10.3.6
Mutable and Complex Joins
Some mutable joins have already appeared in the section discussing outer
joins, but more detail is warranted at this point. A
mutable join
is a join of
more than two tables. The word
mutable
means “subject to change.” Per-
haps the person originally applying the term
mutable
to these types of joins
was implying that these types of joins should be changed. Multiple-table
mutable joins affect performance, usually adversely.
A complex join is by definition a two-table or mutable join containing
extra filtering using Boolean logic AND, OR, IN, and EXISTS clause filter-
