Database Reference
In-Depth Information
Figure 6.44
Find repeated values by using self-join.
S1.StudentID
=
C1.StudentID
S2.StudentID
=
C2.StudentID
To eliminate duplicates such as the pairs (Smith, Green) and (Green, Smith), the WHERE
clause should include a search condition as shown below:
S1.StudentID
<
S2.StudentID
As shown in the output, the classmates are all paired up.
Another use of self-join is to locate repeated values in a column. For example, you can use
self-join to ind the last names of faculty members who teach more than one class. he self-join
illustrated in Figure 6.44 will accomplish the task. he aliases F1 and F2 are used to distinguish
the same table FACULTY_CLASS in the self-join. he following condition
F1.ClassID
<>
F2.ClassID
is used to eliminate faculty members who only teach one class.
As you can see in the output of the query, Fry and Smith each teaches three classes. Lee teaches
two classes. Garza's name is not on the list since she teaches one class.
In addition to operators, subqueries, and table joins, SQL also provides built-in functions to
enhance the database query process. he descriptions of these built-in functions will be given in
the next section.
6.5 SQL Built-In Functions
SQL Database provides a function library where built-in functions are categorized into three
types: aggregate, rowset, and scalar. You will irst learn about the aggregate functions.
